Dateandtimefunctions

Youcanusethedateandtimefunctionstoperformvariousoperationsondatesandtimes
intheTransformerstage.
Functionsthatspecifydates,times,ortimestampsintheargumentsusestringswith
specificformats:

Foradate,theformatis%
yyyy
%
mm
%
dd

Foratime,theformatis%
hh
:%
nn
:%
ss
.Ifextendedtoincludemicroseconds,theformat
is%
hh
:%
nn
:%
ss.x
where
x
givesthenumberofdecimalplacessecondsisgivento.
Foratimestamp,theformatis%
yyyy
%
mm
%
dd
%
hh
:%
nn
:%
ss
.Ifextendedtoinclude
microseconds,theformatis%
yyyy
%
mm
%
dd
%
hh
:%
nn
:%
ss.x
,where
x
givesthe
numberofdecimalplacessecondsisgivento.

Functionsthathavedaysofweekintheargumenttakeastringspecifyingthedayofthe
week.Thedayisspecifiedasathreeletterabbreviation,orthefullname.Forexample,
thestrings"thu"and"thursday"arebothvalid.
ThefollowingfunctionsareintheDate&Timecategoryoftheexpressioneditor.Square
bracketsindicateanargumentisoptional.Theexamplesshowthefunctionasitappears
inaDerivationfieldintheTransformerstage.
CurrentDate
Returnsthedatethatthejobrunsindateformat.

Input
:

Output
:date

Examples
.Usethisfunctiontoaddanewcolumncontainingthedateto
thedataoutputbytheTransformerstage:
CurrentDate()

CurrentTime
Returnsthetimeatwhichthejobrunsintimeformat.

Input
:

Output
:time

Examples
.Usethisfunctiontoaddanewcolumncontainingthetimeto
thedataoutputbytheTransformerstage:
CurrentTime()
CurrentTimeMS
Returnsthetimeatwhichthejobrunsintimeformat,thetimeincludes
microseconds.

Input
:

Output
:time

Examples
.Usethisfunctiontoaddanewcolumncontainingthetimeto
thedataoutputbytheTransformerstage.YoumustsettheExtendedfield
inthecolumnmetadatatoMicrosecondstocontainthefulltime:
CurrentTimeMS()
CurrentTimestamp
Returnsatimestampgivingthedateandtimethatthejobrunsintimestamp
format.

Input
:

Output
:timestamp

Examples
.Usethisfunctiontoaddanewcolumncontainingthe
timestamptothedataoutputbytheTransformerstage:
CurrentTimestamp()
CurrentTimestampMS
Returnsatimestampgivingthedateandtimethatthejobrunsintimestamp
format,thetimepartincludesmicroseconds.

Input
:

Output
:timestamp

Examples
.Usethisfunctiontoaddanewcolumncontainingthe
timestamptothedataoutputbytheTransformerstage.Youmustsetthe
ExtendedfieldinthecolumnmetadatatoMicrosecondstocontainthefull
timestamp.
CurrentTimestampMS()
DateFromDaysSince
Returnsadatebyaddinganintegertoabaselinedate.Theintegercanbenegative
toreturnadatethatisearlierthanthebasedate.

Input
:number(int32),[baseline_date(date)]

Output
:date

Examples
.Ifmylink.myintcolcontainstheinteger18250,and
mylink.mydatecolcontainsthedate19580818,thenthethreefollowing
functionsareequivalent,andreturnthedate20080805:
DateFromDaysSince(18250,"19580818")
DateFromDaysSince(mylink.myintcol,"19580818")
DateFromDaysSince(mylink.myintcol,mylink.mydatecol)
Ifmylink.mynegintcolcontainstheinteger1,andmylink.mydatecol
containsthedate19580818,thenthefollowingthreefunctionsare
equivalent,andreturnthedate19580817:
DateFromDaysSince(1,"19580818")
DateFromDaysSince(mylink.mynegintcol,"19580818")
DateFromDaysSince(mylink.mynegintcol,mylink.mydatecol)
DateFromComponents

Returnsadatefromthegivenyears,months,anddayofmonthgivenasthree
separatevalues.

Input
:years(int32),months(int32),dayofmonth(int32)

Output
:date

Examples
.Ifmylink.yearcolcontainsthevalue2010,mylink.monthcol
containsthevalue12,andmylink.dayofmonthcolcontainsthevalue2,
thenthetwofollowingfunctionsareequivalent,andreturnthedate
20101202.
DateFromComponents(2010,12,2)

DateFromComponents(mylink.yearcol,mylink.monthcol,
mylink.dayofmonthcol)

DateFromJulianDay
Returnsadatefromthegivenjulianday.

Input
:julianday(uint32)

Output
:date

Examples
.Ifmylink.myjulcolcontainsthevalue2454614,thenthetwo
followingfunctionsareequivalent,andreturnthedate20080527.
DateFromJulianDay(2454614)
DateFromJulianDay(mylink.myjulcol)
DateOffsetByComponents
Returnsthegivendate,withoffsetsappliedfromthegivenyearoffset,month
offset,anddayofmonthoffsetgivenasthreeseparatevalues.Theoffsetvalues
caneachbepositive,zero,ornegative.

Input
:basedate(date),year_offset(int32),month_offset(int32),
dayofmonth_offset(int32)

Output
:date

Examples
.Ifmylink.basedatecontains20110818andmylink.yearos
containsthevalue2,mylink.monthoscontainsthevalue0,and
mylink.dayofmonthosolcontainsthevalue0,thenthetwofollowing
functionsareequivalent,andreturnthedate20130818.
DateOffsetByComponents("20110818",2011,8,18)

DateOffsetByComponents(mylink.basedate,mylink.yearos,
mylink.monthos,
mylink.dayofmonthos)

Ifmylink.basedatecontains20110818andmylink.yearoscontainsthe
value2,mylink.monthoscontainsthevalue0,and
mylink.dayofmonthosolcontainsthevalue0,thenthetwofollowing
functionsareequivalent,andreturnthedate20090818.
DateOffsetByComponents("20110818",2011,8,18)
DateOffsetByComponents(mylink.basedate,mylink.yearos,
mylink.monthos,
mylink.dayofmonthos)

DaysSinceFromDate
Returnsthenumberofdaysfromsourcedatetothegivendate.

Input
:source_date,given_date

Output
:days_since(int32)

Examples
.Ifmylink.mysourcedatecontainsthedate19580818and
mylink.mygivendatecontainsthedate20080818,thenthetwo
followingfunctionsareequivalent,andreturntheintegervalue18263.
DaysSinceFromDate(mylink.mysourcedate,mylink.mygivendate)
DaysSinceFromDate("19580818","20080818")

DaysInMonth
Returnsthenumberofdaysinthemonthinthegivenbasedate.

Input
:basedate(date)

Output
:daysinmonth(int32)

Examples
.Ifmylink.mysourcedatecontainsthedate19580818,then
thetwofollowingfunctionsareequivalent,andreturntheintegervalue31.
DaysInMonth(mylink.mysourcedate)
DaysInMonth("19580818")

DaysInYear
Returnsthenumberofdaysintheyearinthegivenbasedate.

Input
:basedate(date)

Output
:daysinyearh(int32)

Examples
.Ifmylink.mysourcedatecontainsthedate20120818,then
thetwofollowingfunctionsareequivalent,andreturntheintegervalue
366.
DaysInYear(mylink.mysourcedate)
DaysInYear("20120818")
Ifmylink.mysourcedatecontainsthedate20110818,thenthetwo
followingfunctionsareequivalent,andreturntheintegervalue365.
DaysInYear(mylink.mysourcedate)
DaysInYear("20110818")
DateOffsetByDays
Returnsthegivendate,offsetbythegivennumberofdays.Theoffsetvaluecan
bepositive,zero,ornegative.

Input
:basedate(date),dayoffset(int32)

Output
:date

Examples
.Ifmylink.basedatecontains20110818andmylink.dayoffset
containsthevalue2,thenthetwofollowingfunctionsareequivalent,and
returnthedate20110820.
DateOffsetByDays("20110818",2)
DateOffsetByDays(mylink.basedate,mylink.dayoffset)

Ifmylink.basedatecontains20110818andmylink.dayoffsetcontainsthe
value31,thenthetwofollowingfunctionsareequivalent,andreturnthe
date20110718.
DateOffsetByDays("20110818",31)
DateOffsetByDays(mylink.basedate,mylink.dayoffset)
HoursFromTime
Returnsthehourportionofatime.

Input
:time

Output
:hours(int8)

Examples
.Ifmylink.mytimecontainsthetime22:30:00,thenthe
followingtwofunctionsareequivalent,andreturntheintegervalue22.
HoursFromTime(mylink.mytime)
HoursFromTime("22:30:00")
JulianDayFromDate
Returnsajuliandayfromthegivendate.

Input
:date

Output
:julianday(int32)

Examples
.Ifmylink.mydatecontainsthedate20080527,thenthetwo
followingfunctionsareequivalent,andreturnthevalue2454614.
JulianDayFromDate("20080527")
JulianDayFromDate(mylink.mydate)
MicroSecondsFromTime
Returnsthemicrosecondportionofatime.

Input
:time

Output
:microseconds(int32)

Examples
.Ifmylink.mytimecontainsthetime22:30:00.32,thenthe
followingfunctionreturnsthevalue320000:
MicroSecondsFromTime(mylink.mytime)
MidnightSecondsFromTime
Returnsthenumberofsecondsfrommidnighttothegiventime.

Input
:time

Output
:seconds(int8)

Examples
.Ifmylink.mytimecontainsthetime00:30:52,thenthetwo
followingfunctionsareequivalent,andreturnthevalue1852:
MidnightSecondsFromTime("00:30:52")
MidnightSecondsFromTime(mylink.mytime)
MinutesFromTime
Returnstheminuteportionofatime.

Input
:time

Output
:minutes(int8)

Examples
.Ifmylink.mytimecontainsthetime22:30:52,thenthetwo
followingfunctionsareequivalent,andreturnthevalue30:
MinutesFromTime("22:30:52")
MinutesFromTime(mylink.mytime)
MonthDayFromDate
Returnsthedayofthemonthfromthegivendate.

Input
:date

Output
:day(int8)

Examples
.Ifmylink.mydatecontainsthedate20080818,thenthetwo
followingfunctionsareequivalent,andreturnthevalue18:
MonthDayFromDate("20080818")
MonthDayFromDate(mylink.mydate)
MonthFromDate
Returnsthemonthnumberfromthegivendate.

Input
:date

Output
:month_number(int8)

Examples
.Ifmylink.mydatecontainsthedate20080818,thenthetwo
followingfunctionsareequivalent,andreturnthevalue8:
MonthFromDate("20080818")
MonthDayDate(mylink.mydate)
NextWeekdayFromDate
Returnsthedateofthespecifieddayoftheweeksoonestafterthesourcedate.
Thedayoftheweekisspecifiedasthefullname,forexample,thursday,ora
threeletterabbreviation,forexample,thu.

Input
:sourcedate(date),day_of_week(string)

Output
:date

Examples
.Ifmylink.mysourcedatecontainsthedate20080818,thenthe
twofollowingfunctionsareequivalent,andreturnthevalue20080821:
NextWeekdayFromDate("20080818","thursday")
NextWeekdayFromDate(mylink.mysourcedate,"thu")
NthWeekdayFromDate
Returnsthedateofthespecifieddayoftheweekoffsetbythespecifiednumber
ofweeksfromthesourcedate.Thedayoftheweekisspecifiedasthefullname,
forexample,thursday,orathreeletterabbreviation,forexample,thu.Theoffset
canbepositive,negative,orzero.

Input
:basedate(date),day_of_week(string),week_offset(int32)

Output
:date

Examples
.Ifmylink.mydatecontainsthedate20090818,thenthetwo
followingfunctionsareequivalent,andreturnthevalue20090827:

NthWeekdayFromDate("20090818","thursday",1)
NthWeekdayFromDate(mylink.mydate,"thu",1)

Ifmylink.mydatecontainsthedate20090818,thenthetwofollowing
functionsareequivalent,andreturnthevalue20090806:
NthWeekdayFromDate("20090818","thursday",2)
NthWeekdayFromDate(mylink.mydate,"thu",2)
PreviousWeekdayFromDate
Returnsthedateofthespecifieddayoftheweekmostrecentbeforethesource
date.Thedayoftheweekisspecifiedasthefullname,forexample,thursday,ora
threeletterabbreviation,forexample,thu.

Input
:sourcedate,day_of_week(string)

Output
:date

Examples
.Ifmylink.mysourcedatecontainsthedate20080818,thenthe
twofollowingfunctionsareequivalent,andreturnthevalue20080814:
PreviousWeekdayFromDate("20080818","thursday")
PreviousWeekdayFromDate(mylink.mysourcedate,"thu")
SecondsFromTime
Returnsthesecondsportionofatime.

Input
:time

Output
:seconds(dfloat)

Examples
.Ifmylink.mytimecontainsthetime22:30:52,thenthetwo
followingfunctionsareequivalent,andreturnthevalue52:
SecondsFromTime("22:30:52")
SecondsFromTime(mylink.mytime)
SecondsSinceFromTimestamp
Returnsthenumberofsecondsbetweentwotimestamps.

Input
:timestamp,timestamp_base

Output
:seconds(dfloat)

Examples
.Ifmylink.mytimestampcontainsthetimestamp20080818
22:30:52,andmylink.mytimestamp_basecontainsthetimestamp
2008081922:30:52,thenthetwofollowingfunctionsareequivalent,
andreturnthevalue86400:

SecondsSinceFromTimestamp("2008081822:30:52","20080819
22:30:52")
SecondsSinceFromTimestamp(mylink.mytimestamp,
mylink.mytimestamp_base)

TimeDate
Returnsthesystemtimeanddateasaformattedstring.

Input
:

Output
:systemtimeanddate(string)

Examples
.Ifthejobwasrunat4.21pmonJune20th2008,thenthe

followingfunctionreturnsthestring"16:21:4820Jun2008".
TimeDate()
TimeFromComponents
Returnsatimefromthegivenhours,minutes,secondsandmicrosecondsgivenas
fourseparatevalues.

Input
:hours(int32),minutes(int32),seconds(int32),microseconds
(int32)

Output
:time

Examples
.Ifmylink.hourcolcontainsthevalue10,mylink.mincol
containsthevalue12,mylink.seccolcontainsthevalue2,and
mylink.mseccolcontains0,thenthetwofollowingfunctionsare
equivalent,andreturnthetime10:12:02.0:
TimeFromComponents(10,12,2,0)

TimeFromComponents(mylink.hourcol,mylink.mincol,
mylink.seccol,mylink.mseccol)

TimeFromMidnightSeconds
Returnsthetimegiventhenumberofsecondssincemidnight.

Input
:seconds(dfloat)

Output
:time

Examples
.Ifmylink.mymidnightsecondscontainsthevalue240,thenthe
twofollowingfunctionsareequivalent,andreturnthevalue00:04:00:
TimeFromMidnightSeconds("240")
TimeFromMidnightSeconds(mylink.mymidnightseconds)
TimeOffsetByComponents
Returnsthegiventime,withoffsetsappliedfromthegivenhouroffset,minute
offset,andsecondoffset,eachgivenasseparatevalues.Thesecondsoffsetcan
includepartialseconds.

Input
:basetime(time),hour_offset(int32),minute_offset(int32),
second_offset(dfloat)

Output
:time

Examples
.Ifmylink.basetimecontains14:05:29andmylink.houros
containsthevalue2,mylink.minoscontainsthevalue0,mylink.secos
containsthevalue20,thenthetwofollowingfunctionsareequivalent,and
returnthetime16:05:49.
TimeOffsetByComponents("14:05:29",2,0,20)

TimeOffsetByComponents(mylink.basetime,mylink.houros,
mylink.minos,mylink.secos)

TimeOffsetBySeconds
Returnsthegiventime,withoffsetsappliedfromthegivensecondsoffset.The
secondsoffsetcanincludepartialseconds.

Input
:basetime(time),second_offset(dfloat)

Output
:time

Examples
.Ifmylink.basetimecontains14:05:29.30andmylink.secos
containsthevalue2.5,thenthetwofollowingfunctionsareequivalent,
andreturnthetime14:05:31.80:
TimeOffsetByComponents("14:05:29.30",2.5)
TimeOffsetByComponents(mylink.basetime,mylink.secos)
TimestampFromDateTime
Returnsatimestampformthegivendateandtime.

Input
:datetime

Output
:timestamp

Examples
.Ifmylink.mydatecontainsthedate20080818and
mylink.mytimecontainsthetime22:30:52,thenthetwofollowing
functionsareequivalent,andreturnthetimestamp2008081822:30:52:
TimestampFromDateTime("20080818","22:30:52")
TimestampFromDateTime(mylink.mydate,mylink.mytime)
TimestampFromSecondsSince
Returnsatimestampderivedfromthenumberofsecondsfromthebase
timestamp.

Input
:seconds(dfloat),[base_timestamp]

Output
:timestamp

Examples
.Ifmylink.mysecondscontainsthevalue2563and
mylink.timestamp_basecontainsthetimestamp2008081822:30:52,
thenthetwofollowingfunctionsareequivalent,andreturnthetimestamp
2008081823:13:35:
TimestampFromSecondsSince("2563","2008081822:30:52")

TimestampFromSecondsSince(mylink.myseconds,mylink.timestamp_b
ase)

TimestampFromTimet
ReturnsatimestampfromthegivenUNIXtime_tvalue.

Input
:timet(int32)

Output
:timestamp

Examples
.Ifmylink.mytimetcontainsthevalue1234567890,thenthe
twofollowingfunctionsareequivalent,andreturnthetimestamp
2009021323:31:30:
TimestampFromTimet("1234567890")
TimestampFromTimet(mylink.mytimet)
TimestampOffsetByComponents
Returnsthegiventimestamp,withoffsetsappliedfromthegivenyearoffset,
monthoffset,dayoffset,houroffset,minuteoffset,andsecondoffset,eachgiven
asseparatevalues.Thesecondsoffsetcanincludepartialseconds.

Input
:basetimestamp(timestamp),year_offset(int32),month_offset

(int32),dayofmonth_offset(int32),hour_offset(int32),minute_offset
(int32),second_offset(dfloat)

Output
:timestamp

Examples
.Ifmylink.basetimestampcontains2009081814:05:29and
mylink.yearoscontains0,mylink.monthoscontainsthevalue2,
mylink.dayoscontainsthevalue4,mylink.houroscontainsthevalue2,
mylink.minoscontainsthevalue0,mylink.secoscontainsthevalue20,
thenthetwofollowingfunctionsareequivalent,andreturnthetimestamp
2009101416:05:49.

TimestampOffsetByComponents("2009081814:05:29",0,2,4,
2,0,20)
TimestampOffsetByComponents(mylink.basetimestamp,
mylink.houros,
mylink.minos,mylink.secos)

TimestampOffsetBySeconds
Returnsthegiventimestamp,withoffsetsappliedfromthegivensecondsoffset.
Thesecondsoffsetcanincludepartialseconds.

Input
:basetimestamp(timestamp),second_offset(dfloat)

Output
:timestamp

Examples
.Ifmylink.basetimestampcontains2009081814:05:29and
mylink.secoscontainsthevalue32760,thenthetwofollowingfunctions
areequivalent,andreturnthetimestamp2009081823:11:29:
TimeOffsetBySeconds("2009081814:05:29",32760)
TimeOffsetBySeconds
(mylink.basetimestamp,mylink.secos)
TimetFromTimestamp
ReturnsaUNIXtime_tvaluefromthegiventimestamp.

Input
:timestamp

Output
:timet(int32)

Examples
.Ifmylink.mytimestampcontainsthevalue20090213
23:31:30,thenthetwofollowingfunctionsareequivalent,andreturnthe
value1234567890:
TimestampFromTimet("2009021323:31:30")
TimestampFromTimet(mylink.mytimestamp)
WeekdayFromDate
Returnsthedaynumberoftheweekfromthegivendate.Origin_dayoptionally
specifiesthedayregardedasthefirstintheweekandisSundaybydefault.

Input
:date,[origin_day]

Output
:day(int8)

Examples
.Ifmylink.mydatecontainsthedate20080818,thenthetwo
followingfunctionsareequivalent,andreturnthevalue1:

WeekdayFromDate("20080818")
WeekdayFromDate(mylink.mydate)

Ifmylink.mydatecontainsthedate20080818,andmylink.origin_day
containssaturday,thenthetwofollowingfunctionsareequivalent,and
returnthevalue2:
WeekdayFromDate("20080818","saturday")
WeekdayFromDate(mylink.mydate,mylink.origin_day)
YeardayFromDate
Returnsthedaynumberintheyearfromthegivendate.

Input
:date

Output
:day(int16)

Examples
.Ifmylink.mydatecontainsthedate20080818,thenthetwo
followingfunctionsareequivalent,andreturnthevalue231:
YeardayFromDate("20080818")
YeardayFromDate(mylink.mydate)
YearFromDate
Returnstheyearfromthegivendate.

Input
:date

Output
:year(int16)

Examples
.Ifmylink.mydatecontainsthedate20080818,thenthetwo
followingfunctionsareequivalent,andreturnthevalue2008:
YearFromDate("20080818")
YearFromDate(mylink.mydate)
YearweekFromDate
Returnstheweeknumberintheyearfromthegivendate

Input
:date

Output
:week(int16)

Examples
.Ifmylink.mydatecontainsthedate20080818,thenthetwo
followingfunctionsareequivalent,andreturnthevalue33:
YearweekFromDate("20080818")
YearweekFromDate(mylink.mydate)

Logicalfunctions
Thelogicalfunctionsperformbitoperations.
ThelogicalfunctionsareintheLogicalcategoryoftheexpressioneditor.Squarebrackets
indicateanargumentisoptional.Theexamplesshowthefunctionasitappearsina
DerivationfieldintheTransformerstage.
BitAnd
ReturnsthebitwiseANDofthetwointegerarguments.

Input
:number1(uint64),number2(uint64)

Output
:number(uint64)

Examples
.Ifmylink.mynumber1containsthenumber352and
mylink.mynumber2containsthenumber400,thenthefollowingtwo
functionsareequivalent,andreturnthevalue256:
BitAnd(352,400)
BitAnd(mylink.mynumber1,mylink.mynumber2)

BitCompress
Returnstheintegermadefromthestringargument,whichcontainsabinary
representationof"1"sand"0"s.

Input
:string

Output
:number(uint64)

Examples
.Ifmylink.mynumber1containsthestring"0101100000",then
thefollowingtwofunctionsareequivalent,andreturnthenumber352.
BitExpand("0101100000")
BitExpand(mylink.mynumber)

BitExpand
Returnsastringcontainingthebinaryrepresentationin"1"sand"0"softhegiven
integer.

Input
:number(uint64)

Output
:string

Examples
.Ifmylink.mynumber1containsthenumber352,thenthe
followingtwofunctionsareequivalent,andreturnthestring
"0101100000".
BitExpand(352)
BitExpand(mylink.mynumber)

BitOr
ReturnsthebitwiseORofthetwointegerarguments.

Input
:number1(uint64),number2(uint64)

Output
:number(uint64)

Examples
.Ifmylink.mynumber1containsthenumber352and
mylink.mynumber2containsthenumber400,thenthefollowingtwo
functionsareequivalent,andreturnthevalue496:
BitOr(352,400)
BitOr(mylink.mynumber1,mylink.mynumber2)

BitXOr
ReturnsthebitwiseExclusiveORofthetwointegerarguments.

Input
:number1(uint64),number2(uint64)

Output
:number(uint64)

Examples
.Ifmylink.mynumber1containsthenumber352and
mylink.mynumber2containsthenumber400,thenthefollowingtwo

functionsareequivalent,andreturnthevalue240:
BitXOr(352,400)
BitXOr(mylink.mynumber1,mylink.mynumber2)

Not
Returnsthecomplementofthelogicalvalueofanexpression.Ifthevalueof
expressionistrue,theNotfunctionreturnsavalueoffalse(0).Ifthevalueof
expressionisfalse,theNOTfunctionreturnsavalueoftrue(1).Anumeric
expressionthatevaluatesto0isalogicalvalueoffalse.Anumericexpression
thatevaluatestoanythingelse,otherthanthenullvalue,isalogicaltrue.An
emptystringislogicallyfalse.Allotherstringexpressions,includingstringsthat
includeanemptystring,spaces,orthenumber0andspaces,arelogicallytrue.

Input
:expression

Output
:complement(int8)

Examples
.Ifmylink.myexpressioncontainstheexpression55,thenthe
followingtwofunctionsareequivalent,andreturnthevalue1:
Not(55)
Not(mylink.myexpression)
Ifmylink.myexpressioncontainstheexpression5+5,thenthefollowing
twofunctionsareequivalent,andreturnthevalue0:
Not(5+5)
Not(mylink.myexpression)

SetBit
Returnsanintegerwithspecificbitssettoaspecificstate,where
origfield
isthe
inputvaluetoperformtheactionon,
bitlist
isastringcontainingalistof
commaseparatedbitnumberstosetthestateof,and
bitstate
iseither1or0,
indicatingwhichstatetosetthosebits.

Input
:origfield(uint64),bitlist(string),bitstate(uint8)

Output
:number(uint64)

Examples
.Ifmylink.origfieldcontainsthenumber352,mylink.bitlist
containsthelist"2,4,8",andmylink.bitstatecontainsthevalue1,thenthe
followingtwofunctionsareequivalent,andreturnthevalue494:
SetBit(356,"2,4,8",1)
SetBit(mylink.origfield,mylink.bitlist,mylink.bitstate)

Mathematicalfunctions
Themathematicalfunctionsperformmathematicaloperations.
ThemathematicalfunctionsareintheMathematicalcategoryoftheexpressioneditor.
Squarebracketsindicateanargumentisoptional.Theexamplesshowthefunctionasit
appearsinaDerivationfieldintheTransformerstage.
Abs
Returnstheabsolutevalueofanynumericexpression.Theabsolutevalueofan

expressionisitsunsignedmagnitude.

Input
:numeric_expression(int32)

Output
:result(dfloat)

Examples
.Ifmylink.number1containsthenumber12and
mylink.number2containsthenumber34,thenthefollowingtwofunctions
areequivalent,andreturnthenumber22:
Abs(1234)
Abs(mylink.mynumber1mylink.mynumber2)
Ifmylink.number1containsthenumber34andmylink.number2contains
thenumber12,thenthefollowingtwofunctionsareequivalent,andreturn
thenumber22:

Abs(3412)
Abs(mylink.mynumber1mylink.mynumber2)

Acos
Calculatesthetrigonometricarccosineofanexpression.Theexpressionmustbe
anumericvalue.Theresultisexpressedinradians.

Input
:numeric_expression(dfloat)

Output
:result(dfloat)

Examples
.Ifmylink.numbercontainsthenumber0.707106781,thenthe
followingtwofunctionsareequivalent,andreturnthevalue0.785398:
Acos(0.707106781)
Acos(mylink.mynumber)

Asin
Calculatesthetrigonometricarcsineofanexpression.Theexpressionmustbea
numericvalue.Theresultisexpressedinradians.

Input
:numeric_expression(dfloat)

Output
:result(dfloat)

Examples
.Ifmylink.numbercontainsthenumber0.707106781,thenthe
followingtwofunctionsareequivalent,andreturnthevalue0.785398:
Asin(0.707106781)
Asin(mylink.mynumber)

Atan
Calculatesthetrigonometricarctangentofanexpression.Theexpressionmustbe
anumericvalue.Theresultisexpressedinradians.

Input
:numeric_expression(dfloat)

Output
:result(dfloat)

Examples
.Ifmylink.numbercontainsthenumber135,thenthefollowing
twofunctionsareequivalent,andreturnthevalue1.56339,whichisthe
anglethathasanarctangentof135:
Atan(135)

Atan(mylink.mynumber)

Ceil
Calculatesthesmallestintegervaluegreaterthanorequaltothegivendecimal
value.

Input
:number(dfloat)

Output
:result(int32)

Examples
.Ifmylink.numbercontainsthenumber2355.66,thenthe
followingtwofunctionsareequivalent,andreturnthevalue2356:
Ceil(2355.66)
Ceil(mylink.mynumber)

Cos
Calculatesthetrigonometriccosineofanexpression.Theexpressionmustbea
numericvalue.Theexpressionmustproduceanumericvaluewhichistheangle
inradians.

Input
:radians(dfloat)

Output
:result(dfloat)

Examples
.Ifmylink.numbercontainsthenumber0.785398,thenthe
followingtwofunctionsareequivalent,andreturnthevalue0.7071:
Cos(0.785398)
Cos(mylink.mynumber)

Cosh
Calculatesthehyperboliccosineofanexpression.Theexpressionmustbea
numericvalue.

Input
:number(dfloat)

Output
:result(dfloat)

Examples
.Ifmylink.numbercontainsthenumber2,thenthefollowing
twofunctionsareequivalent,andreturnthevalue3.7622:
Cosh(2)
Cosh(mylink.mynumber)

Div
Outputsthewholepartoftherealdivisionoftworealnumbers(dividend,
divisor).

Input
:dividend(dfloat),divisor(dfloat)

Output
:result(dfloat)

Examples
.Ifmylink.dividendcontainsthenumber100,and
mylink.divisorcontainsthenumber25,thenthefollowingtwofunctions
areequivalent,andreturnthevalue4:
Div(100,25)
Div(mylink.dividend,mylink.divisor)

Exp
Calculatestheresultofbase'e'raisedtothepowerdesignatedbythevalueofthe

expression.Thevalueof'e'isapproximately2.71828.Theexpressionmust
evaluatetoanumericvalue.

Input
:number(dfloat)

Output
:result(dfloat)

Examples
.Ifmylink.numbercontainsthenumber5,thenthefollowing
twofunctionsareequivalent,andreturnthevalue54.5982:
Exp(51)
Exp(mylink.number1)

Fabs
Calculatestheabsolutevalueofthegivenfloatvalue.

Input
:number(dfloat)

Output
:result(dfloat)

Examples
.Ifmylink.numbercontainsthenumber26.53,thenthe
followingtwofunctionsareequivalent,andreturnthevalue26.53:
Fabs(26.53)
Fabs(mylink.number)

Floor
Calculatesthelargestintegervaluelessthanorequaltothegivendecimalvalue.

Input
:number(dfloat)

Output
:result(int32)

Examples
.Ifmylink.numbercontainsthenumber203.25,thenthe
followingtwofunctionsareequivalent,andreturnthevalue203:
Floor(203.25)
Floor(mylink.number)

Ldexp
Returnsadfloatvaluefrommultiplyingthemantissaby2raisedtothepowerof
theexponent.

Input
:mantissa(dfloat),exponent(int32)

Output
:result(dfloat)

Examples
.Ifmylink.mantissacontainsthenumber2,and
mylink.exponentcontainsthenumber3,thenthefollowingtwofunctions
areequivalent,andreturnthevalue16:
Floor(2,3)
Floor(mylink.mantissa,mylink.exponent)

Llabs
Calculatestheabsolutevalueofthegivenintegervalue.

Input
:number(integer)

Output
:result(unsignedinteger)

Examples
.Ifmylink.numbercontainsthenumber26,thenthefollowing

twofunctionsareequivalent,andreturnthevalue26:
Llabs(26)
Llabs(mylink.number)

Ln
Calculatesthenaturallogarithmofanexpressioninbase'e'.Thevalueof'e'is
approximately2.71828.Theexpressionmustevaluatetoanumericvaluegreater
than0.

Input
:number(dfloat)

Output
:result(dfloat)

Examples
.Ifmylink.numbercontainsthenumber6,thenthefollowing
twofunctionsareequivalent,andreturnthevalue1.79176:
Ln(6)
Ln(mylink.number)

Log10
Returnsthelogtothebase10ofthegivenvalue

Input
:number(dfloat)

Output
:result(dfloat)

Examples
.Ifmylink.numbercontainsthenumber6,thenthefollowing
twofunctionsareequivalent,andreturnthevalue0.778151:
Log10(6)
Log10(mylink.number)

Max
Returnsthegreaterofthetwoargumentvalues.

Input
:number1(int32),number2(int32)

Output
:result(int32)

Examples
.Ifmylink.number1containsthenumber6,and
mylink.number1containsthenumber101,thenthefollowingtwo
functionsareequivalent,andreturnthevalue101:
Max(6,101)
Max(mylink.number1,mylink.number2)

Min
Returnsthelowerofthetwoargumentvalues.

Input
:number1(int32),number2(int32)

Output
:result(int32)

Examples
.Ifmylink.number1containsthenumber6,and
mylink.number1containsthenumber101,thenthefollowingtwo
functionsareequivalent,andreturnthevalue6:
Min(6,101)
Min(mylink.number1,mylink.number2)

Mod

Calculatesthemodulo(theremainder)oftwoexpressions(dividend,divisor).

Input
:dividend(int32),divisor(int32)

Output
:result(int32)

Examples
.Ifmylink.dividendcontainsthenumber115,and
mylink.divisorcontainsthenumber25,thenthefollowingtwofunctions
areequivalent,andreturnthevalue15:
Mod(115,25)
Mod(mylink.dividend,mylink.divisor)

Neg
Negatesanumber.

Input
:number(dfloat)

Output
:result(dfloat)

Examples
.Ifmylink.numbercontainsthenumber123,thenthefollowing
twofunctionsareequivalent,andreturnthevalue123:
Neg(123)
Neg(mylink.number)

Pwr
Calculatesthevalueofanexpressionwhenraisedtoaspecifiedpower
(expression,power).

Input
:expression(dfloat),power(dfloat)

Output
:result(dfloat)

Examples
.Ifmylink.expressioncontainsthenumber2,andmylink.power
containsthenumber3,thenthefollowingtwofunctionsareequivalent,
andreturnthevalue8:
Pwr(2,3)
Pwr(mylink.expression,mylink.power)

Rand
32
Returnapsuedorandomintegerbetween0and2
1

Input
:

Output
:result(uint32)

Examples
.Usethisfunctiontoaddacolumntoyouroutputcontaininga
randomnumber:
Rand()

Random
32
Returnsarandomnumberbetween0and2
1

Input
:

Output
:result(uint32)

Examples
.Usethisfunctiontoaddacolumntoyouroutputcontaininga
randomnumber:

Random()

Sin
Calculatesthetrigonometricsineofanexpression.Theexpressionmustbea
numericvalue.Theexpressionmustproduceanumericvaluewhichistheangle
inradians.

Input
:radians(dfloat)

Output
:result(dfloat)

Examples
.Ifmylink.numbercontainsthenumber0.785398,thenthe
followingtwofunctionsareequivalent,andreturnthevalue0.7071:
Sin(0.785398)
Sin(mylink.mynumber)

Sinh
Calculatesthehyperbolicsineofanexpression.Theexpressionmustbea
numericvalue.

Input
:number(dfloat)

Output
:result(dfloat)

Examples
:Ifmylink.numbercontainsthenumber2,thenthefollowing
twofunctionsareequivalent,andreturnthevalue3.62686:
Sinh(2)
Sinh(mylink.mynumber)

Sqrt
Calculatesthesquarerootofanumber.

Input
:number(dfloat)

Output
:result(dfloat)

Examples
:Ifmylink.numbercontainsthenumber450,thenthefollowing
twofunctionsareequivalent,andreturnthevalue21.2132:
Sqrt(450)
Sqrt(mylink.mynumber)

Tan
Calculatesthetrigonometrictangentofanexpression.Theexpressionmust
produceanumericvaluewhichistheangleinradians.

Input
:radians(dfloat)

Output
:result(dfloat)

Examples
.Ifmylink.numbercontainsthenumber0.7853981,thenthe
followingtwofunctionsareequivalent,andreturnthevalue0.7071:
Tan(0.7853981)
Tan(mylink.mynumber)

Tanh
Calculatesthehyperbolictangentofanexpression.Theexpressionmustbea
numericvalue.

Input
:number(dfloat)

Output
:result(dfloat)

Examples
:Ifmylink.numbercontainsthenumber2,thenthefollowing
twofunctionsareequivalent,andreturnthevalue0.964028:
Tanh(2)
Tanh(mylink.mynumber)

Nullhandlingfunctions
YoucanusethenullhandlingfunctionsintheTransformerstagetohandlenullsin
derivations.
Ifyouuseinputcolumnsinanoutputcolumnexpression,anullvalueinanyinput
columncausesanulltobewrittentotheoutputcolumn.Youcan,however,usethenull
handlingfunctionstohandlenullsexplicitly.
ThefollowingfunctionsareavailableintheNullHandlingcategory.Squarebrackets
indicateanargumentisoptional.Theexamplesshowthefunctionasitappearsina
DerivationfieldintheTransformerstage.
IsNotNull
Returnstruewhenanexpressiondoesnotevaluatetothenullvalue.

Input
:any

Output
:true/false(int8)

Examples
.IftheDerivationfieldforanoutputcolumncontainedthe
followingcode,thentheTransformerstagechecksiftheinputcolumn
namedmylink.mycolumncontainsanullvalue.Iftheinputcolumndoes
notcontainanull,theoutputcolumncontainsthevalueoftheinput
column.Iftheinputcolumndoescontainanull,thentheoutputcolumn
containsthestringNULL.
IfIsNotNull(mylink.mycolumn)Thenmylink.mycolumnElse
"NULL"

IsNull
Returnstruewhenanexpressionevaluatestothenullvalue.

Input
:any

Output
:true/false(int8)

Examples
.IftheDerivationfieldforanoutputcolumncontainedthe
followingcode,thentheTransformerstagechecksiftheinputcolumn
namedmylink.mycolumncontainsanullvalue.Iftheinputcolumn
containsanull,theoutputcolumncontainsthestringNULL.Iftheinput
columndoesnotcontainanull,thentheoutputcolumncontainsthevalue
oftheinputcolumn.
IfIsNull(mylink.mycolumn)Then"NULL"Elsemylink.mycolumn

NullToEmpty

Returnsanemptystringiftheinputcolumnisnull,otherwisereturnstheinput
columnvalue.

Input
:inputcolumn

Output
:inputcolumnvalueoremptystring

Examples
.IftheDerivationfieldforanoutputcolumncontainedthe
followingcode,thentheTransformerstagechecksiftheinputcolumn
namedmylink.mycolumncontainsanullvalue.Iftheinputcolumn
containsanull,theoutputcolumncontainsanemptystring.Iftheinput
columndoescontainanull,thentheoutputcolumncontainsthevalue
fromtheinputcolumn.
NullToEmpty(mylink.mycolumn)

NullToZero
Returnszeroiftheinputcolumnisnull,otherwisereturnstheinputcolumnvalue.

Input
:inputcolumn

Output
:inputcolumnvalueorzero

Examples
.IftheDerivationfieldforanoutputcolumncontainedthe
followingcode,thentheTransformerstagechecksiftheinputcolumn
namedmylink.mycolumncontainsanullvalue.Iftheinputcolumn
containsanull,theoutputcolumncontainszero.Iftheinputcolumndoes
containanull,thentheoutputcolumncontainsthevaluefromtheinput
column.
NullToZeroy(mylink.mycolumn)

NullToValue
Returnsthespecifiedvalueiftheinputcolumnisnull,otherwisereturnstheinput
columnvalue.

Input
:inputcolumn,
value

Output
:inputcolumnvalueor
value

Examples
.IftheDerivationfieldforanoutputcolumncontainedthe
followingcode,thentheTransformerstagechecksiftheinputcolumn
namedmylink.mycolumncontainsanullvalue.Iftheinputcolumn
containsanull,theoutputcolumncontains42.Iftheinputcolumndoes
containanull,thentheoutputcolumncontainsthevaluefromtheinput
column.
NullToValue(mylink.mycolumn,42)

SetNull
Assignsanullvaluetothetargetcolumn.

Input
:

Output
:

Examples
.IftheDerivationfieldforanoutputcolumncontainedthe
followingcode,thentheTransformerstagesetstheoutputcolumntonull:

setnull()

Numberfunctions
Usethenumberfunctionstoextractthemantissafromadecimalorfloatingpoint
number.TheNumbercategoryintheexpressioneditoralsocontainsthetypecasting
functions,whichyoucanusetocastnumbersasdouble,float,orintegerdatatypes.
Squarebracketsindicateanargumentisoptional.Theexamplesshowthefunctionasit
appearsinaDerivationfieldintheTransformerstage.
Thetypecastingfunctionshelpyouwhenyouperformmathematicalcalculationsusing
numericfields.Forexample,ifyouhaveacalculationusinganoutputcolumnoftype
floatderivedfromaninputcolumnoftypeintegerinaParallelTransformerstagethe
resultisderivedasanintegerregardlessofitsfloattype.Ifyouwantanonintegralresult
foracalculationusingintegraloperands,youcanusethetypecastingfunctionstocast
theintegeroperandsintononintegraloperands.
AsDouble
Treatthegivennumberasadouble.

Input
:number

Output
:number(double)

Examples
.Inthefollowingexpression,theinputcolumnmynumber
containsaninteger,butthefunctionoutputsadouble.If
mylink.mynumbercontainsthevalue56,thenthefollowingtwofunctions
areequivalent,andreturnthevalue1.29629629629629619E+01:
AsDouble(56/4.32)
AsDouble(mylink.mynumber/4.32)

AsFloat
Treatthegivennumberasafloat.

Input
:number

Output
:number(float)

Examples
.Inthefollowingexpression,theinputcolumnmynumber
containsaninteger,butthefunctionoutputsafloat.Ifmylink.mynumber
containsthevalue56,thenthefollowingtwofunctionsareequivalent,and
returnthevalue1.29629629629629619E+01:
AsFloat(56/4.32)
AsFloat(mylink.mynumber/4.32)

AsInteger
Treatthegivennumberasaninteger.

Input
:number

Output
:number(integer)

Examples
.Inthefollowingexpression,theinputcolumnmynumber
containsadouble,butthefunctionisoutputaninteger.If

mylink.mynumbercontainsthevalue56,thenthefollowingtwofunctions
areequivalent,andreturnthevalue12:
AsInteger(56/4.32)
AsInteger(mylink.mynumber/4.32)
MantissaFromDecimal
Returnsthemantissafromthegivendecimal.

Input
:number(decimal)

Output
:result(dfloat)

Examples
.Ifmylink.numbercontainsthenumber243.7675,thenthe
followingtwofunctionsareequivalent,andreturnthevalue7675:
MantissaFromDecimal(243.7675)
MantissaFromDecimal(mylink.mynumber)
MantissaFromDFloat
Returnsthemantissafromthegivendfloat.

Input
:number(dfloat)

Output
:result(dfloat)

Examples
.Ifmylink.numbercontainsthenumber
1.234412000000000010E+4,thenthefollowingfunctionreturnsthe
value1:
MantissaFromDFloat(mylink.mynumber)

Rawfunctions
UsetheRawfunctiontoobtainthelengthofthedatainacolumncontainingrawdata.
ThefunctionisintheRawcategory.Theexamplesshowthefunctionasitappearsina
DerivationfieldintheTransformerstage.
RawLength
Returnsthelengthofarawstring.

Input
:inputstring(raw)

Output
:result(int32)

Examples
.Ifmylink.rawdatacontainstherawdatafromabitmap,then
thefollowingfunctionreturnsthesizeofthebitmapinbytes:
RawLength(mylink.rawdata)

Stringfunctions
Usethestringfunctionstomanipulatestrings.
ThefollowingfunctionsareintheStringcategoryoftheexpressioneditor.Square
bracketsindicateanargumentisoptional.Theexamplesshowthefunctionasitappears
inaDerivationfieldintheTransformerstage.

AlNum
Checkswhetherthegivenstringcontainsonlyalphanumericcharacters.

Input
:string(string)

Output
:true/false(int8)

Examples
.Ifmylink.mystring1containsthestring"OED_75_9*E",then
thefollowingfunctionwouldreturnthevalue1(false).
AlNum(mylink.mystring1)
Ifmylink.mystring2containsthestring"12redroses",thenthefollowing
functionwouldreturnthevalue1(true).

AlNum(mylink.mystring2)

Alpha
Checkswhetherthegivenstringcontainsonlyalphabeticcharacters.

Input
:string(string)

Output
:true/false(int8)

Examples
.Ifmylink.mystring1containsthestring"12redroses",thenthe
followingfunctionwouldreturnthevalue1(false).
Alpha(mylink.mystring1)
Ifmylink.mystring2containsthestring"twelveredroses",thenthe
followingfunctionwouldreturnthevalue1(true).
Alpha(mylink.mystring2)
CompactWhiteSpace
Returnthestringafterreducingallconsecutivewhitespacetoasinglespace.

Input
:string(string)

Output
:result(string)

Examples
.Ifmylink.mystringcontainsthestring"toomanyspaces",
thenthefollowingfunctionreturnsthestring"toomanyspaces":
CompactWhiteSpace(mylink.mystring)

Compare
Comparestwostringsforsorting.Thecomparisoncanbeleftjustified(the
default)orrightjustified.Arightjustifiedcomparisoncomparesnumeric
substringswithinthespecifiedstringsasnumbers.Thenumericstringsmust
occuratthesamecharacterpositionineachstring.Forexample,arightjustified
comparisonofthestringsAB100andAB99indicatesthatAB100isgreaterthan
AB99since100isgreaterthan99.Arightjustifiedcomparisonofthestrings
AC99andAB100indicatesthatAC99isgreatersinceCisgreaterthanB.

Input
:string1(string),string2(string),[justification(LorR)]

Output
:result(int8),canbe1forstring1islessthanstring2,0forboth
stringsarethesame,1forstring1isgreaterthanstring2.

Examples
.Ifmylink.mystring1containsthestring"AB99"and

mylink.mystring2containsthestring"AB100",thenthefollowing
functionreturnstheresult1.
Compare(mylink.mystring1,mylink.mystring2,L)
Ifmylink.mystring1containsthestring"AB99"andmylink.mystring2
containsthestring"AB100",thenthefollowingfunctionreturnstheresult
1.
Compare(mylink.mystring1,mylink.mystring2,R)
CompareNoCase
Comparestwostringsforsorting,ignoringtheircase.

Input
:string1(string),string2(string)

Output
:result(int8),canbe1forstring1islessthanstring2,0forboth
stringsarethesame,1forstring1isgreaterthanstring2.

Examples
.Ifmylink.mystring1containsthestring"ChocolateCake"and
mylink.mystring2containsthestring"chocolatecake",thenthefollowing
functionreturnstheresult1.
ComparNoCase(mylink.mystring1,mylink.mystring2)

CompareNum
Comparesthefirst
n
charactersoftwostrings.

Input
:string1(string),string2(string),length(int16)

Output
:result(int8),canbe1forstring1islessthanstring2,0forboth
stringsarethesame,1forstring1isgreaterthanstring2.

Examples
.Ifmylink.mystring1containsthestring"Chocolate"and
mylink.mystring2containsthestring"ChoccyTreat",thenthefollowing
functionreturnstheresult1.
ComparNum(mylink.mystring1,mylink.mystring2,4)
CompareNumNoCase
Comparesthefirst
n
charactersoftwostrings,ignoringtheircase.

Input
:string1(string),string2(string),length(int16)

Output
:result(int8),canbe1forstring1islessthanstring2,0forboth
stringsarethesame,1forstring1isgreaterthanstring2.

Examples
.Ifmylink.mystring1containsthestring"chocolate"and
mylink.mystring2containsthestring"ChoccyTreat",thenthefollowing
functionreturnstheresult1.
ComparNumNoCase(mylink.mystring1,mylink.mystring2,4)

Convert
Convertscharactersinthestringdesignatedin
expression
.Convertsthecharacters
specifiedin
fromlist
tothecharactersspecifiedin
tolist
.

Input
:fromlist(string),tolist(string),expression(string)

Output
:result(string)

Examples
.Ifmylink.mystring1containsthestring"NOWISTHE

TIME",thenthefollowingfunctionreturnsthestring"NOWYSXHE
XYME".
Convert("TI","XY",mylink.mystring1)
Count
Countsthenumberoftimesasubstringoccursinastring.

Input
:string(string),substring(string)

Output
:result(int32)

Examples
.Ifmylink.mystring1containsthestring"chocolatedrops,
chocolateicecream,chocolatebars",thenthefollowingfunctionreturns
3.
Count(mylink.mystring1,"choc")

Dcount
Countsthenumberofdelimitedfieldsinastring.

Input
:string(string),delimiter(string)

Output
:result(int32)

Examples
.Ifmylink.mystring1containsthestring"chocolatedrops,
chocolateicecream,chocolatebars",thenthefollowingfunctionreturns
3.
Dcount(mylink.mystring1,",")

DownCase
Changesalluppercaselettersinastringtolowercase.

Input
:string(string)

Output
:result(string)

Examples
.Ifmylink.mystring1containsthestring"CaMelcAsE",then
thefollowingfunctionreturnsthestring"camelcase".
DownCase(mylink.mystring1)

DQuote
Enclosesastringindoublequotationmarks.

Input
:string(string)

Output
:result(string)

Examples
.Ifmylink.mystring1containsthestringneedsquotes,thenthe
followingfunctionreturnsthestring"needsquotes".
DQuote(mylink.mystring1)

Field
Returnsoneormoresubstringslocatedbetweenspecifieddelimitersinastring.
Theargument
occurrence
specifieswhichoccurrenceofthedelimiteristobe
usedasaterminator.Theargument
number
optionallyspecifieshowmany
substringstoreturn.

Input
:string(string),delimiter(string),occurrence(int32),[number

(int32)]

Output
:result(string)

Examples
.Ifmylink.mystring1containsthestring"chocolatedrops,
chocolateicecream,chocolatebars,chocolatedippers",thenthefollowing
functionreturnsthestring"chocolateicecream".
Field(mylink.mystring1,",",2)
Ifmylink.mystring1containsthestring"chocolatedrops,chocolateice
cream,chocolatebars,chocolatedippers",thenthefollowingfunction
returnsthestring"chocolateicecream,chocolatebars".
Field(mylink.mystring1,",",2,2)

Index
Findsthestartingcharacterpositionofasubstring.Theargument
occurrence
specifieswhichoccurrenceofthesubstringistobelocated.

Input
:string(string)substring(string)occurrence(int32)

Output
:result(int32)

Examples
.Ifmylink.mystring1containsthestring"chocolatedrops,
chocolateicecream,chocolatebars,chocolatedippers",thenthefollowing
functionreturnsthevalue18.
Index(mylink.mystring1,"chocolate",2)

Left
Returnstheleftmost
n
charactersofastring.

Input
:string(string)number(int32)

Output
:result(string)

Examples
.Ifmylink.mystring1containsthestring"chocolatedrops,
chocolateicecream,chocolatebars,chocolatedippers",thenthefollowing
functionreturnsthestring"chocolate".
Left(mylink.mystring1,9)

Len
Returnsthelengthofastringincharacters.

Input
:string(string)

Output
:result(int32)

Examples
.Ifmylink.mystring1containsthestring"chocolate",thenthe
followingfunctionreturnsthevalue9.
Len(mylink.mystring1)

Num
Returns1ifstringcanbeconvertedtoanumber,or0otherwise.

Input
:string(string)

Output
:result(int32)

Examples
.Ifmylink.mystring1containsthestring"22",thenthe

followingfunctionreturnsthevalue1.
Num(mylink.mystring1)
Ifmylink.mystring1containsthestring"twentytwo",thenthefollowing
functionreturnsthevalue0.
Num(mylink.mystring1)
PadString
Returnthestringpaddedwiththespecifiednumberofpadcharacters.

Input
:string(string)padstring(string)padlength(int32)

Output
:result(string)

Examples
.IIfmylink.mystring1containsthestring"AB175",thenthe
followingfunctionreturnsthestring"AB17500000".
PadString(mylink.mystring1,"0",5)

Right
Returnstherightmost
n
charactersofastring.

Input
:string(string)number(int32)

Output
:result(string)

Examples
.Ifmylink.mystring1containsthestring"chocolatedrops,
chocolateicecream,chocolatebars,chocolatedippers",thenthefollowing
functionreturnsthestring"dippers".
Right(mylink.mystring1,7)

Soundex
Returnsacodewhichidentifiesasetofwordsthatare(roughly)phonetically
alikebasedonthestandard,openalgorithmforSOUNDEXevaluation.

Input
:string(string)

Output
:result(string)

Examples
.Ifmylink.mystring1containsthestring"Griffin"thenthe
followingfunctionreturnsthecode"G615".
Soundex(mylink.mystring1)
Ifmylink.mystring1containsthestring"Griphin"thenthefollowing
functionalsoreturnsthecode"G615".
Soundex(mylink.mystring1)

Space
Returnsastringof
n
spacecharacters.

Input
:length(int32)

Output
:result(string)

Examples
.Ifmylink.mylengthcontainsthenumber100,thenthe
followingfunctionreturnsastringthatcontains100spacecharacters.
Space(mylink.mylength)

SQuote
Enclosesastringinsinglequotationmarks.

Input
:string(string)

Output
:result(string)

Examples
.Ifmylink.mystring1containsthestringneedsquotes,thenthe
followingfunctionreturnsthestring'needsquotes'.
SQuote(mylink.mystring1)

Str
Repeatsastringthespecifiednumberoftime.

Input
:string(string)

Output
:result(string)

Examples
.Ifmylink.mystring1containsthestringneeds"choc",thenthe
followingfunctionreturnsthestring"chocchocchocchocchoc".
Str(mylink.mystring1,5)
StripWhiteSpace
Returnsthestringafterremovingallwhitespacecharactersfromit.

Input
:string(string)repeats(int32)

Output
:result(string)

Examples
.Ifmylink.mystringcontainsthestring"toomanyspaces",
thenthefollowingfunctionreturnsthestring"toomanyspaces":
StripWhiteSpace(mylink.mystring)

Trim
Removeallleadingandtrailingspacesandtabsplusreduceinternaloccurrences
toone.Theargument
stripchar
optionallyspecifiesacharacterotherthanaspace
oratab.Theargument
options
optionallyspecifiesthetypeoftrimoperationto
beperformedandcontainsoneormoreofthefollowingvalues:
A
Removealloccurrencesof
stripchar
B
Removebothleadingandtrailingoccurrencesof
stripchar
D
Removeleading,trailing,andredundantwhitespacecharacters
E
Removetrailingwhitespacecharacters
F
Removeleadingwhitespacecharacters
L
Removeallleadingoccurrencesof
stripchar
R
Removeleading,trailing,andredundantoccurrencesof
stripchar
T
Removealltrailingoccurrencesof
stripchar

Input
:string(string)[stripchar(string)][options(string)]

Output
:result(string)

Examples
.Ifmylink.mystringcontainsthestring"Stringwithwhitespace
",thenthefollowingfunctionreturnsthestring"Stringwithwhitespace":
Trim(mylink.mystring)

Ifmylink.mystringcontainsthestring"..Remove..redundant..dots....",then
thefollowingfunctionreturnsthestring"Remove.redundant.dots":
Trim(mylink.mystring,".")
Ifmylink.mystringcontainsthestring"Remove..all..dots....",thenthe
followingfunctionreturnsthestring"Removealldots":
Trim(mylink.mystring,".","A")

Ifmylink.mystringcontainsthestring"Remove..trailing..dots....",thenthe
followingfunctionreturnsthestring"Remove..trailing..dots":
Trim(mylink.mystring,".","T")
TrimB
Removesalltrailingspacesandtabsfromastring.

Input
:string(string)

Output
:result(string)

Examples
.Ifmylink.mystringcontainsthestring"toomanytrailing
spaces",thenthefollowingfunctionreturnsthestring"toomany
trailingspaces":
TrimB(mylink.mystring)

TrimF
Removesallleadingspacesandtabsfromastring.

Input
:string(string)

Output
:result(string)

Examples
.Ifmylink.mystringcontainsthestring"toomanyleading
spaces",thenthefollowingfunctionreturnsthestring"toomanyleading
spaces":
TrimF(mylink.mystring)
TrimLeadingTrailing
Removesallleadingandtrailingspacesandtabsfromastring.

Input
:string(string)

Output
:result(string)

Examples
.Ifmylink.mystringcontainsthestring"toomanyspaces",
thenthefollowingfunctionreturnsthestring"toomanyspaces":
TrimLeadingTrailing(mylink.mystring)

UpCase
Changesalllowercaselettersinastringtouppercase.

Input
:string(string)

Output
:result(string)

Examples
.Ifmylink.mystring1containsthestring"CaMelcAsE",then
thefollowingfunctionreturnsthestring"CAMELCASE".
UpCase(mylink.mystring1)

Vectorfunction
Usethevectorfunctiontoaccessanelementinavectorcolumn.
ThefunctionisintheVectorcategoryoftheexpressioneditor.Theexamplesshowthe
functionasitappearsinaDerivationfieldintheTransformerstage.
ElementAt
Accessesanelementofavector.Thevectorindexstartsat0.Thefunctioncanbe
usedaspartof,orthewholeofanexpression.

Input
:input_column(columnname)index(int)

Output
:elementofvector

Examples
.Thefollowingexampleoutputsthesecondelementofthe
vectorinthecolumnmylink.myvector.
ElementAt(mylink.myvector,2)
Thefollowingexampleoutputsthesecondelementofthevectorinthe
columnmylink.myvectorandaddsoneit.
ElementAt(mylink.myvector,2)+1

Typeconversionfunctions
Usethetypeconversionfunctionstochangethetypeofanargument.
ThefollowingfunctionsareintheTypeConversioncategoryoftheexpressioneditor.
Squarebracketsindicateanargumentisoptional.Thedefaultdateformatis
%yyyy%mm%dd.
TheexamplesshowthefunctionasitappearsinaDerivationfieldintheTransformer
stage.
Char
GeneratesanASCIIcharacterfromitsnumericcodevalue.Youcanoptionally
specifytheallow8bitsargumenttoconvert8bitASCIIvalues.

Input
:code(number),[allow8bits]

Output
:result(char)

Examples
.ThefollowingexampleoutputstheASCIIcode65asthe
characterA.
Char(65)

DateToString
Returnsthestringrepresentationofthegivendate.Theformatofthestringcan
optionallybespecified.

Input
:date(date),[format(string)]

Output
:result(string)

Examples
.Thefollowingexampleoutputsthedatecontainedinthe

columnmylink.mydatetoastring.Ifmylink.mydatecontainsthedate18th
August,2009,thentheoutputstringis"20090818":
DateToString(mylink.mydate)
Thefollowingexampleoutputsthedatecontainedinthecolumn
mylink.mydatetoastringwiththeformatdd:mm:yyyy.Ifmylink.mydate
containedthedate18thAugust,2009,thentheoutputstringwouldbe
"18:08:2009":
DateToString(mylink.mydate,"%dd:%mm:%yyyy")
DateToDecimal
Returnsthegivendateasapackeddecimalvalue.Ifyourtargetdecimalspecifies
ascale,partofthedateappearsafterthedecimalpoint.Youcanoptionally
specifyaformatstringthatspecifieshowthedateisstoredinthedecimalnumber.
Thedefaultformatstringis"%yyyy%mm%dd",so,forexample,thedate
20090825isstoredasthedecimalnumber20090825.Formatstringscanonly
specifyaformatthatcontainsnumbers.Forexample,youcannotspecifyaformat
stringsuchas"%yyyy%mm%dd",becausethehyphencharacter()cannotbe
storedinapackeddecimalvalue.Thefollowingtokensarevalidforconversions
toorfromdecimalvalues:
%yyyy(fourdigityear)
%yy(twodigityear)
%NNNNyy(twodigityearwithcutoff)
%mm(twodigitmonth)
%dd(twodigitdayofmonth)
%ddd(threedigitdayofyear)
Theliteraldigits0to9arealsovalid.

Input
:basedate(date)[,format(string)]

Output
:converted_date(decimal)

Examples
.Ifthecolumnmylink.basedatecontainsthedate20120818,
thenthefollowingfunctionstoresthedateasthedecimalnumber
18082012:
DateToDecimal(mylink.basedate,"%dd%mm%yyyy")
Ifthecolumnmylink.basedatecontainsthedate20120818,andthetarget
columnhasalengthof10andascaleof2,thenthefollowingfunction
storesthedateasthedecimalnumber201208.18:
DateToDecimal(mylink.basedate)
DecimalToDate
Returnsthegivenpackeddecimalasadate.Boththesignandthescaleofthe
decimalnumberareignoredwhenitisconvertedtoadate.Youcanoptionally
specifyaformatstringthatspecifieshowthedateisstoredinthedecimalnumber.
Thedefaultformatstringis"%yyyy%mm%dd",so,forexample,thedate
20090825isstoredasthedecimalnumber20090825.Formatstringscanonly

specifyaformatthatcontainsnumbers.Forexample,youcannotspecifyaformat
stringsuchas"%yyyy%mm%dd",becausethehyphencharacter()cannotbe
storedinapackeddecimalvalue.Thefollowingtokensarevalidforconversions
toorfromdecimalvalues:
%yyyy(fourdigityear)
%yy(twodigityear)
%NNNNyy(twodigityearwithcutoff)
%mm(twodigitmonth)
%dd(twodigitdayofmonth)
%ddd(threedigitdayofyear)
Theliteraldigits0to9arealsovalid.

Input
:basedec(decimal)[,format(string)]

Output
:date

Examples
.Ifthecolumnmylink.mydecdatacontainsthevalue18082012,
thenthefollowingfunctionreturnsthedate20120818:
DecimalToDate(mylink.basedate,"%dd%mm%yyyy")
Ifthecolumnmylink.mydecdatacontainsthevalue201208.18,thenthe
followingfunctionreturnsthedate20120818:
DecimalToDate(mylink.basedate)
DecimalToDecimal
Returnsthegivendecimalindecimalrepresentationwithprecisionandscale
specifiedinthetargetcolumndefinition.Theargument
rtype
optionallyspecifies
aroundingtype,andissettooneofthefollowingvalues:

ceil
.Roundthesourcefieldtowardpositiveinfinity.Forexample,1.4>2,1.6
>1.
floor
.Roundthesourcefieldtowardnegativeinfinity.Forexample,1.6>1,1.4
>2.
round_inf
.Roundortruncatethesourcefieldtowardthenearestrepresentable
value,breakingtiesbyroundingpositivevaluestowardpositiveinfinityand
negativevaluestowardnegativeinfinity.Forexample,1.4>1,1.5>2,1.4>
1,1.5>2.
trunc_zero
.Discardanyfractionaldigitstotherightoftherightmostfractional
digitsupportedinthedestination,regardlessofsign.Forexample,ifthe
destinationisaninteger,allfractionaldigitsaretruncated.Ifthedestinationis
anotherdecimalwithasmallerscale,roundortruncatetothescalesizeofthe
destinationdecimal.Forexample,1.6>1,1.6>1.

Input
:decimal(decimal)[,rtype(string)]

Output
:result(decimal)

Examples
.Ifthecolumnmylink.mydeccontainsthedecimalnumber

2.5345,thefollowingfunctionreturnsthedecimalnumber00000002.54.
DecimalToDecimal(mylink.mydec,"ceil")
Thefollowingfunctionreturnsthedecimalnumber00000002.53.
DecimalToDecimal(mylink.mydec,"floor")
Thefollowingfunctionreturnsthedecimalnumber00000002.53.
DecimalToDecimal(mylink.mydec,"trunc_zero")
Thefollowingfunctionreturnsthedecimalnumber00000002.53.
DecimalToDecimal(mylink.mydec,"round_inf")
Inalltheseexamples,thetargetdecimalhasalengthof10andascaleof
2.
DecimalToDFloat
Returnsthegivendecimalindfloatrepresentation.Theargument"fix_zero"
optionallyspecifiesthatallzerodecimalvaluesareregardedasvalid(bydefault,
decimalnumberscomprisingallzerosaretreatedasinvalid).

Input
:decimal(decimal)[,"fix_zero"]

Output
:result(dfloat)

Examples
.Ifthecolumnmylink.mydeccontainsthedecimalnumber
00000004.00thefollowingfunctionreturnsthedfloatnumber
4.00000000000000000E+00.
DecimalToDFloat(mylink.mydec,"fix_zero")
Ifthecolumnmylink.mydeccontainsthedecimalnumber00012344.00
thefollowingfunctionreturnsthedfloatnumber
1.23440000000000000E+04.
DecimalToDFloat(mylink.mydec,"fix_zero")
Ifthecolumnmylink.mydeccontainsthedecimalnumber00012344.120
thefollowingfunctionreturnsthedfloatnumber
1.23441200000000010E+04.
DecimalToDFloat(mylink.mydec,"fix_zero")
Ifthecolumnmylink.mydeccontainsthedecimalnumber00012344.120
thefollowingfunctionreturnsthedfloatnumber
1.23441200000000010E+04.
DecimalToDFloat(mylink.mydec)
Ifthecolumnmylink.mydeccontainsthedecimalnumber00012344.000
thefollowingfunctionreturnsthedfloatnumber
1.23440000000000000E+04.
DecimalToDFloat(mylink.mydec)
DecimalToString
Returnsthegivendecimalasastring.Theargument"fix_zero"optionally
specifiesthatallzerodecimalvaluesareregardedasvalid(bydefault,decimal
numberscomprisingallzerosaretreatedasinvalid).Thiscoversthecasewhere
thesignbitsofthepackeddecimalrepresentationareall0aswellasallthe
contentdigits.Thiscastisnotconsideredvalidunless"fix_zero"istrue.

Input
:decimal(decimal)[,"fix_zero"]

Output
:result(string)

Examples
.Ifthecolumnmylink.mydeccontainsthedecimalnumber
00000004.00,thefollowingfunctionreturnsthestring"4":
DecimalToString(mylink.mydec,"suppress_zero")
Ifthecolumnmylink.mydeccontainsthedecimalnumber00000004.00,
thefollowingfunctionreturnsthestring
"0000000000000000000000000004.0000000000".
DecimalToString(mylink.mydec,"fix_zero")
Ifthecolumnmylink.mydeccontainsthedecimalnumber00012344.00,
thefollowingfunctionreturnsthestring"12344".
DecimalToString(mylink.mydec,"suppress_zero")
Ifthecolumnmylink.mydeccontainsthedecimalnumber00012344.00,
thefollowingfunctionreturnsthestring
"0000000000000000000000012344.0000000000".
DecimalToString(mylink.mydec,"fix_zero")
Ifthecolumnmylink.mydeccontainsthedecimalnumber00012344.120,
thefollowingfunctionreturnsthestring
"0000000000000000000000012344.1200000000".
DecimalToString(mylink.mydec,"fix_zero")
Ifthecolumnmylink.mydeccontainsthedecimalnumber00012344.120,
thefollowingfunctionreturnsthestring"12344.12":
DecimalToString(mylink.mydec,"suppress_zero")
Ifthecolumnmylink.mydeccontainsthedecimalnumber00012344.120,
thefollowingfunctionreturnsthestring
"0000000000000000000000012344.120000000".
DecimalToString(mylink.mydec)
Ifthecolumnmylink.mydeccontainsthedecimalnumber00012344.000,
thefollowingfunctionreturnsthestring
"0000000000000000000000012344.0000000000".
DecimalToString(mylink.mydec)
DecimalToTime
Returnsthegivenpackeddecimalasatime.Youcanoptionallyspecifyaformat
stringthatspecifieshowthetimeisstoredinthedecimalnumber.Thedefault
formatstringis"%hh%nn%ss",so,forexample,thetime14:03:22isstoredas
thedecimalnumber140322.Formatstringscanonlyspecifyaformatthat
containsnumbers.Forexample,youcannotspecifyaformatstringsuchas
"%hh:%nn:%ss",becausethecoloncharacter(:)cannotbestoredinapacked
decimalvalue.Thefollowingtokensarevalidforconversionstoorfromdecimal
values:

%hh(twodigithoursusing24hourclock)
%nn(twodigitminutes)
%ss(twodigitseconds)
%ss.
N
(twodigitseconds,plusthenumberoffractionaldigitsallowed.The
numberoffractionaldigitsisfromonetosixinclusive).

Theliteraldigits0to9arealsovalid.
Ifyourspecifiedformatincludesmicroseconds(forexample,%ss.4),thenthe
positionofthedecimalpointisinferredinthedecimalvalue.Thepositionofthe
decimalpointdoesnothavetocoincidewiththespecifiedscaleofthedecimal
(forexample,scale=4).

Input
:time(time)[,format(string)]

Output
:result(decimal)

Examples
:Ifthecolumnmylink.mytimedeccontainsthedecimalvalue
200658,thenthefollowingfunctionreturnsthetime20:06:58:
DecimalToTime(mylink.mytimedec)
Ifthecolumnmylink.mytimedeccontainsthedecimalvalue580620,then
thefollowingfunctionreturnsthetime20:06:58:
DecimalToTime(mylink.mytimedec,"%ss%nn%hh")
DecimalToTimestamp
Returnsthegivenpackeddecimalasatimestamp.Youcanoptionallyspecifya
formatstringthatspecifieshowthetimestampisstoredinthedecimalnumber.
Thedefaultformatstringis"%yyyy%mm%dd%hh%nn%ss",so,forexample,
thetimestamp2009082514:03:22isstoredasthedecimalnumber
20090825140322.Formatstringscanonlyspecifyaformatthatcontainsnumbers.
Forexample,youcannotspecifyaformatstringsuchas
"%yyyy/%mm/%dd%hh:%nn:%ss",becausetheslashcharacter(/)andthecolon
character(:)cannotbestoredinapackeddecimalvalue.Thefollowingtokensare
validforconversionstoorfromdecimalvalues:

%yyyy(fourdigityear)
%yy(twodigityear)
%NNNNyy(twodigityearwithcutoff)
%mm(twodigitmonth)
%dd(twodigitdayofmonth)
%ddd(threedigitdayofyear)
%hh(twodigithoursusing24hourclock)
%nn(twodigitminutes)
%ss(twodigitseconds)
%ss.
N
(twodigitseconds,plusthenumberoffractionaldigitsallowed.The
numberoffractionaldigitsisfromonetosixinclusive).
Theliteraldigits0to9arealsovalid.
Ifyourspecifiedformatincludesmicroseconds(forexample,%ss.4),thenthe
positionofthedecimalpointisinferredinthedecimalvalue.Thepositionofthe
decimalpointdoesnothavetocoincidewiththespecifiedscaleofthedecimal
(forexample,scale=4).

Input
:timestamp(timestamp)[,format(string)]

Output
:result(decimal)

Examples
:Ifthecolumnmylink.mytimestampdeccontainsthevalue
19580818200658,thenthefollowingfunctionreturnsthetimestamp
1958081820:06:58:
DecimalToTimestamp(mylink.mytimestampdec)
Ifthecolumnmylink.mytimestampdeccontainsthedecimalvalue
200658580818,thenthefollowingfunctionreturnsthetimestamp
1958081820:06:58:
DecimalToTimestamp(mylink.mytimestampdec,
"%hh%nn%ss%yy%mm%dd")

DFloatToDecimal
Returnsthegivendfloatindecimalrepresentation.Theargument
rtype
optionally
specifiesaroundingtype,andissettooneofthefollowingvalues:
ceil
.Roundthesourcefieldtowardpositiveinfinity.Forexample,1.4>2,1.6
>1.
floor
.Roundthesourcefieldtowardnegativeinfinity.Forexample,1.6>1,1.4
>2.
round_inf
.Roundortruncatethesourcefieldtowardthenearestrepresentable
value,breakingtiesbyroundingpositivevaluestowardpositiveinfinityand
negativevaluestowardnegativeinfinity.Forexample,1.4>1,1.5>2,1.4>
1,1.5>2.
trunc_zero
.Discardanyfractionaldigitstotherightoftherightmostfractional
digitsupportedinthedestination,regardlessofsign.Forexample,ifthe
destinationisaninteger,allfractionaldigitsaretruncated.Ifthedestinationis
anotherdecimalwithasmallerscale,roundortruncatetothescalesizeofthe
destinationdecimal.Forexample,1.6>1,1.6>1.

Input
:number(dfloat),[rtype(string)]

Output
:result(decimal)

Examples
.Ifthecolumnmylink.myfloatcontainsthedfloatnumber
2.534,thefollowingfunctionreturnsthedecimalnumber00000002.54.
DFloatToDecimal(mylink.mydec,"ceil")
Ifthecolumnmylink.myfloatcontainsthedfloatnumber2.534,the
followingfunctionreturnsthedecimalnumber00000002.53.
DFloatToDecimal(mylink.mydec,"floor")
Ifthecolumnmylink.myfloatcontainsthedfloatnumber2.534,the
followingfunctionreturnsthedecimalnumber00000002.53.
DFloatToDecimal(mylink.mydec,"trunc_zero")
Ifthecolumnmylink.myfloatcontainsthedfloatnumber2.534,the
followingfunctionreturnsthedecimalnumber00000002.53.
DFloatToDecimal(mylink.mydec,"round_inf")
DfloatToStringNoExp

Returnsthegivendfloatinitsstringrepresentationwithnoexponent,usingthe
specifiedscale.

Input
:number(dfloat),scale(string)

Output
:result(string)

Examples
.Ifthecolumnmylink.myfloatcontainsthedfloatnumber
2.534,thenthefollowingfunctionreturnsthestring00000002.50:
DfloatToStringNoExp(mylink.myfloat,2)

IsValid
Returnswhetherthegivenstringisvalidforthegiventype.Validtypesare
"date","decimal","dfloat","sfloat","int8","uint8","int16","uint16","int32",
"uint32","int64","uint64","raw","string","time","timestamp","ustring".For
datatypesofdate,time,andtimestamp,youcanoptionallyspecifyaformat
string.Theformatstringdescribestheformatthatyourinputdatauseswhenit
differsfromthedefaultformatsfordate,time,ortimestamp.Thedefaultformat
fordateis%yyyy%mm%dd.Thedefaultformatfortimeis"%hh:%mm:%ss".
Thedefaultformatfortimestampis%yyyy%mm%dd%hh:%mm:%ss".

Input
:type(string),teststring(string)[,format(string)]

Output
:result(int8)

Examples
.Ifthecolumnmylink.mystringcontainsthestring"1",thenthe
followingfunctionreturnsthevalue1.
IsValid("int8",mylink.mystring)
Ifthecolumnmylink.mystringcontainsthestring"380096.06",thenthe
followingfunctionreturnsthevalue0.
IsValid("int8",mylink.mystring)

IsValidDate
Returnswhetherthegivenvalueisvalidforthetypedate.

Input
:testdate(date)

Output
:result(int8)

Examples
.Ifthecolumnmylink.mydatecontainsthedate20110913,
thenthefollowingfunctionreturnsthevalue1.
IsValidDate(mylink.mydate)
Ifthecolumnmylink.mydatecontainsthestring"380096.06",thenthe
followingfunctionreturnsthevalue0,becausetheconvertedstringisnot
avaliddate.
IsValidDate(StringTodate(mylink.mydate))
IsValidDecimal
Returnswhetherthegivenvalueisvalidforthetypedecimal.Iftheallzerosflagis
setto0,thenanallzeroesrepresentationisnotvalid.Theallzerosflagissetto
zerobydefault.

Input
:testvalue(decimal)[,allzerosflag(uint8)]

Output
:result(int8)

Examples
.Ifthecolumnmylink.mynumcontainsthevalue310007.65,
thenthefollowingfunctionreturnsthevalue1.
IsValidDecimal(mylink.mynum)
Ifthecolumnmylink.mynumcontainsthestring"wakerobin",thenthe
followingfunctionreturnsthevalue0,becausetheconvertedstringisnot
avaliddecimal.
IsValidDecimal(StringToDecimal(mylink.mynum))

IsValidTime
Returnswhetherthegiventimeisvalidforthetypetime.

Input
:testtime(time)

Output
:result(int8)

Examples
.Ifthecolumnmylink.mytimecontainsthetime23:09:22,then
thefollowingfunctionreturnsthevalue1:
IsValidTime(mylink.mytime)
Ifthecolumnmylink.mydatecontainsthestring"IbnKayeed",thenthe
followingfunctionreturnsthevalue0,becausetheconvertedstringisnot
avalidtime.
IsValidTime(StringToTime(mylink.mytime))
IsValidTimestamp
Returnswhetherthegiventimestampisvalidforthetypetimestamp.

Input
:testtimestamp(timestamp)

Output
:result(int8)

Examples
.Ifthecolumnmylink.mytimestampcontainsthetime
2011091323:09:22,thenthefollowingfunctionreturnsthevalue1:
IsValidTimestamp(mylink.mytimestamp)
Ifthecolumnmylink.mytimestampcontainsthestring"oneoftwo",then
thefollowingfunctionreturnsthevalue0,becausetheconvertedstringis
notavalidtimestamp.
IsValidTimestamp(StringToTimestamp(mylink.mytimestamp))

RawNumAt
Returnstheintegervalueatthespecifiedindexvalueinthespecifiedrawfield.
Theindexstartsat0.

Input
:rawfield(raw),index(int32)

Output
:result(int32)

Examples
.Ifthecolumnmylink.myrawcontainsarawvaluederived
fromthestring"hello",thenthefollowingfunctionreturnstheinteger
0x68(theASCIIcodeforthecharacterh):
RawNumAt(mylink.myraw,0)
Ifthecolumnmylink.myrawcontainsarawvaluederivedfromthestring
"hello",thenthefollowingfunctionreturns0becausethespecifiedindex

isoutofrange:

RawNumAt(mylink.myraw,12)
RawToString
Returnsthegivenrawvalueasastringrepresentation.Youmustensurethatthe
rawinputvaluecontainsasequenceofbytesthatarevalidascharactersinthe
targetcharactersetinwhichtheoutputstringisused.Forexample,therawvalue
{0xE00x410x42}isnotavalidsequenceofUTF8characters,sincethelead
byte,0xE0,issupposedtobefollowedbyabyteintherange[0x80..0xBF].Ifa
rawvalue{xE0x41x42}ispassedtotheRawToStringfunction,therecouldbe
anerroriftheoutputstringisthenaccessedasifitwereencodedinUTF8.

Input
:rawfield(raw)

Output
:result(string)

Examples
.Ifthecolumnmylink.myrawcontainsthevalue{0x310x31
0x300x350x320x320x300x39},thenthefollowingfunctionreturnsthe
string"11052209".
RawNumAt(mylink.myraw)

Seq
GeneratesanumericcodevaluefromanASCIIcharacter.Youcanoptionally
specifytheallow8bitsargumenttoconvert8bitASCIIvalues.

Input
:Seq(char)

Output
:result(number)

Examples
.ThefollowingexampleoutputsthecharacterAastheASCII
code65.
Seq("A")

SeqAt
Returnsthenumericcodepointvalueofthecharacteratthespecifiedpositionin
thegivenstring.Theindexstartsat0.Ifthespecifiedindexisoutofrange,the
functionreturns0.

Input
:basestring(string),index(int32)

Output
:result(int32)

Examples
.Ifthecolumnmylink.mystringcontainsthestring"horse",then
thefollowingfunctionreturnsthevalue0x6F(thatis,theASCIIvalueof
thecharactero).
SeqAt(mylink.mystring,1)

StringToDate
Returnsadatefromthegivenstringinthegivenformat.Youdonothaveto
specifyaformatstringifyourstringcontainsadateinthedefaultformat
yyyymmdd.

Input
:string(string)[,format(string)]

Output
:result(date)

Examples
:Ifthecolumnmylink.mystringcontainsthestring
19580818,thenthefollowingfunctionreturnsthedate19580818.
StringToDate(mylink.mystring)
Ifthecolumnmylink.mystringcontainsthestring18:08:1958,thenthe
followingfunctionreturnsthedate19580818.
StringToDate(mylink.mystring,"%dd:%mm:%yyyy")
StringToDecimal
Returnsthegivenstringasadecimalrepresentation.Theargument
rtype
optionallyspecifiesaroundingtype,andissettooneofthefollowingvalues:

ceil
.Roundthesourcefieldtowardpositiveinfinity.Forexample,1.4>2,1.6
>1.
floor
.Roundthesourcefieldtowardnegativeinfinity.Forexample,1.6>1,1.4
>2.
round_inf
.Roundortruncatethesourcefieldtowardthenearestrepresentable
value,breakingtiesbyroundingpositivevaluestowardpositiveinfinityand
negativevaluestowardnegativeinfinity.Forexample,1.4>1,1.5>2,1.4>
1,1.5>2.
trunc_zero
.Discardanyfractionaldigitstotherightoftherightmostfractional
digitsupportedinthedestination,regardlessofsign.Forexample,ifthe
destinationisaninteger,allfractionaldigitsaretruncated.Ifthedestinationis
anotherdecimalwithasmallerscale,roundortruncatetothescalesizeofthe
destinationdecimal.Forexample,1.6>1,1.6>1.

Input
:string(string),[rtype(string)]

Output
:result(decimal)

Examples
.Ifthecolumnmylink.mystringcontainsthestring"19982.22",
andthetargetisdefinedashavingaprecisionof7andascaleof2,then
thefollowingfunctionreturnsthedecimal19983.22.
StringToDecimal(mylink.mystring)
Ifthecolumnmylink.mystringcontainsthestring"19982.2276",andthe
targetisdefinedashavingaprecisionof7andascaleof2,thenthe
followingfunctionreturnsthedecimal19983.23.
StringToDecimal(mylink.mystring,"ceil")

StringToRaw
Returnsastringinrawrepresentation.

Input
:string(string)

Output
:result(raw)

Examples
.Ifthecolumnmylink.mystringcontainsthestring"hello",and
thetargetcolumnisdefinedasbeingoftypeBinarythenthefollowing
functionreturnsthevalue{0x680x650x6C0x6C0x6F}.
StringToRaw(mylink.mystring)

StringToTime

Returnsatimerepresentationofthegivenstring.

Input
:string(string),[format(string)]

Output
:result(time)

Examples
:Ifthecolumnmylink.mystringcontainsthestring"20:06:58",
thenthefunctionreturnsatimeof20:06:58.
StringToTime(mylink.mystring)
Ifthecolumnmylink.mystringcontainsthestring"20:6:58",thenthe
functionreturnsatimeof20:06:58.
StringToTime(mylink.mystring,"%(h,s):$(n,s):$(s,s)")
StringToTimestamp
Returnsatimerepresentationofthegivenstring.

Input
:string(string)[format(string)]

Output
:result(time)

Examples
:Ifthecolumnmylink.mystringcontainsthestring
"1958080820:06:58",thenthefunctionreturnsthetimestamp
1958080820:06:58.
StringToTimestamp(mylink.mystring)
Ifthecolumnmylink.mystringcontainsthestring"8/8/195820:6:58",
thenthefunctionreturnsthetimestamp1958080820:06:58.
StringToTimestamp(mylink.mystring,
"%(d,s)/%(m,s)/%yyyy%(h,s):$(n,s):$(s,s)")

StringToUstring
Returnsaustringfromthegivenstring,optionallyusingthespecifiedmap
(otherwiseusesprojectdefault).

Input
:string(string),[mapname(string)]

Output
:result(ustring)

Examples
:Ifthecolumnmylink.mystringcontainsthestring"11052009",
thenthefollowingfunctionreturnstheustring"11052009"
StringToUstring(mylink.mystring)
TimestampToDate
Returnsadatefromthegiventimestamp.

Input
:timestamp(timestamp)

Output
:result(date)

Examples
:Ifthecolumnmylink.mytimestampcontainsthetimestamp
1958081820:06:58,thenthefollowingfunctionreturnsthedate
19580818:
TimestampToDate(mylink.mytimestamp)
TimestampToDecimal
Returnsthegiventimestampasapackeddecimal.Youcanoptionallyspecifya
formatstringthatspecifieshowthetimestampisstoredinthedecimalnumber.

Thedefaultformatstringis"%yyyy%mm%dd%hh%nn%ss",so,forexample,
thetimestamp2009082514:03:22isstoredasthedecimalnumber
20090825140322.Formatstringscanonlyspecifyaformatthatcontainsnumbers.
Forexample,youcannotspecifyaformatstringsuchas
"%yyyy/%mm/%dd%hh:%nn:%ss",becausetheslashcharacter(/)andthecolon
character(:)cannotbestoredinapackeddecimalvalue.Thefollowingtokensare
validforconversionstoorfromdecimalvalues:
%yyyy(fourdigityear)
%yy(twodigityear)
%NNNNyy(twodigityearwithcutoff)
%mm(twodigitmonth)
%dd(twodigitdayofmonth)
%ddd(threedigitdayofyear)
%hh(twodigithoursusing24hourclock)
%nn(twodigitminutes)
%ss(twodigitseconds)
%ss.
N
(twodigitseconds,plusthenumberoffractionaldigitsallowed.The
numberoffractionaldigitsisfromonetosixinclusive).
Theliteraldigits0to9arealsovalid.
Ifyourspecifiedformatincludesmicroseconds(forexample,%ss.4),thenthe
positionofthedecimalpointisinferredinthedecimalvalue.Thepositionofthe
decimalpointdoesnothavetocoincidewiththespecifiedscaleofthedecimal
(forexamplescale=4).

Input
:timestamp(timestamp)[,format(string)]

Output
:result(decimal)

Examples
:Ifthecolumnmylink.mytimestampcontainsthetimestamp
1958081820:06:58,thenthefollowingfunctionreturnsthedecimal
value19580818200658:
TimestampToDecimal(mylink.mytimestamp)
Ifthecolumnmylink.mytimestampcontainsthetimestamp19580818
20:06:58,thenthefollowingfunctionreturnsthedecimalvalue
200658580818:

TimestampToDecimal(mylink.mytimestamp,"%hh%nn%ss%yy%mm%dd")

TimestampToString
Returnsastringfromthegiventimestamp.

Input
:timestamp(timestamp)[format(string)]

Output
:result(string)

Examples
:Ifthecolumnmylink.mytimestampcontainsthetimestamp

1958081820:06:58,thenthefunctionreturnsthestring
"1958081820:06:58".
TimestampToString(mylink.mytimestamp)
Ifthecolumnmylink.mytimestampcontainsthetimestamp
1958081820:06:58,thenthefunctionreturnsthestring"18/08/1958
20:06:58":
TimestampToString(mylink.mytimestamp,"%dd/%mm/%yyyy
%hh:$nn:$ss")

TimestampToTime
Returnsthestringrepresentationofthegiventimestamp.

Input
:timestamp(timestamp)

Output
:result(time)

Examples
:Ifthecolumnmylink.mytimestampcontainsthetimestamp
1958081820:06:58,thenthefunctionreturnsthetime20:06:58:
TimestampToTime(mylink.mytimestamp)

TimeToString
Returnsastringfromthegiventime.

Input
:timestamp(timestamp)[format(string)]

Output
:result(time)

Examples
:Ifthecolumnmylink.mytimecontainsthetime20:06:58,then
thefollowingfunctionreturnsthestring"20:06:58":
TimeToString(mylink.mytime)
Ifthecolumnmylink.mytimecontainsthetime20:06:58,thenthe
followingfunctionreturnsthestring"58:06:20":
TimeToString(mylink.mytime,"%ss:$nn:$hh")
TimeToDecimal
Returnsthegiventimeasapackeddecimal.Youcanoptionallyspecifyaformat
stringthatspecifieshowthetimeisstoredinthedecimalnumber.Thedefault
formatstringis"%hh%nn%ss",so,forexample,thetime14:03:22isstoredas
thedecimalnumber140322.Formatstringscanonlyspecifyaformatthat
containsnumbers.Forexample,youcannotspecifyaformatstringsuchas
"%hh:%nn:%ss",becausethecoloncharacter(:)cannotbestoredinapacked
decimalvalue.Thefollowingtokensarevalidforconversionstoorfromdecimal
values:

%hh(twodigithoursusing24hourclock)
%nn(twodigitminutes)
%ss(twodigitseconds)
%ss.
N
(twodigitseconds,plusthenumberoffractionaldigitsallowed.The
numberoffractionaldigitsisfromonetosixinclusive).
Theliteraldigits0to9arealsovalid.

Ifyourspecifiedformatincludesmicroseconds(forexample,%ss.4),thenthe
positionofthedecimalpointisinferredinthedecimalvalue.Thepositionofthe
decimalpointdoesnothavetocoincidewiththespecifiedscaleofthedecimal
(forexamplescale=4).

Input
:time(time)[,format(string)]

Output
:result(decimal)

Examples
:Ifthecolumnmylink.mytimecontainsthetime20:06:58,then
thefollowingfunctionreturnsthedecimalvalue200658:
TimeToDecimal(mylink.mytime)
Ifthecolumnmylink.mytimecontainsthetime20:06:58,thenthe
followingfunctionreturnsthedecimalvalue580620:
TimeToDecimal(mylink.mytime,"%ss%nn%hh")
UstringToString
Returnsastringfromthegivenustring,optionallyusingthespecifiedmap
(otherwiseusesprojectdefault).

Input
:string(ustring)[,mapname(string)]

Output
:result(string)

Examples
:Ifthecolumnmylink.myustringcontainstheustring
"11052009",thenthefollowingfunctionreturnsthestring"11052009":
UstringToString(mylink.myustring)

Utilityfunctions
Theutilityfunctionshaveavarietyofpurposes.
ThefollowingfunctionsareavailableintheUtilitycategory(squarebracketsindicatean
argumentisoptional):
GetEnvironment
Returnsthevalueofthegivenenvironmentvariable.

Input
:environmentvariable(string)

Output
:result(string)

Examples
.Ifyouqueriedthevalueoftheenvironmentvariablename
APT_RDBMS_COMMIT_ROWSthenthefollowingderivationmight
returnthevalue"2048".
GetEnvironment("APT_RDBMS_COMMIT_ROWS")
GetSavedInputRecord
Thisfunctionisusedtoimplementtheaggregatingofdataontheinputlinkofa
Transformerstage.YoucalltheGetsSavedInputRecordfunctiontoaretrievea
copyofaninputrowthatyouhavepreviouslysavedtoacachearea.Thefunction
retrievesthenextinputrowfromthecache(intheorderinwhichtheyweresaved
tothecache)andmakesitthecurrentinputrow.Theretrievedrowoverrideswhat
wasthecurrentinputrow,andsoanyderivationusinganinputcolumnvaluewill

usethevalueofthatcolumnintheinputrowretrievedfromthecache,notwhat
waspreviouslythecurrentinputrow.YoumustcallGetSavedInputRecordina
loopvariablederivation,youcannotcallitfromanywhereelse.Forexample,you
cannotcallGetSavedInputRecordintheLoopConditionexpression.Youcancall
GetSavedInputRecord,multipletimesandretrievethenextcachedrowoneach
call.UsetheSaveInputRecordfunctiontostorerowstothecache.
GetSavedInputRecordreturnsthecacheindexnumberoftherecordretrieved
fromthatcache.

Input
:

Output
:cache_index_number

Examples
.Thefollowingexampleisthederivationofaloopvariable
namedSavedRecordIndexinaTransformerstage:
SavedRecordIndex:GetSavedInputRecord()

NextSKChain
ThisfunctionisusedintheSlowlyChangingDimensionstageasthederivation
foracolumnwiththeSKChainpurposecode.Thefunctionisnotusedinthe
Transformerstage.NextSKChainreturnsthevalueofthesurrogatekeycolumn
forthenextrowinthechain,orthevaluethathasbeenspecifiedtouseforthe
lastrecordinthechain.

Input
:last_chain_value(int64)

Output
:surrogate_key_value(int64)

Examples
.Ifyouspecifythefollowingfunctioninthederivationfieldfor
aSKChaincolumninanSCDstage,theoutputcolumncontainsthevalue
ofthesurrogatekeyofthenextrecordinthechain,orthevalue180858if
thisisthelastrowinthechain.
NextSKChain(180858)
NextSurrogateKey
Returnsthevalueofthenextsurrogatekey.Youmusthavepreviouslysetupyour
surrogatekeysource,anddefineddetailsontheSurrogateKeytaboftheStage
pageoftheTransformerpropertieswindow.

Input
:

Output
:surrogate_key_value(int64)

Example
.Thederivationfieldofyoursurrogatekeycolumncontainsthe
followingfunction:
NextSurrogateKey()

PrevSKChain
ThisfunctionisusedintheSlowlyChangingDimensionstageasthederivation
foracolumnwiththeSKChainpurposecode.Thefunctionisnotusedinthe
Transformerstage.PrevSKChainReturnsthevalueofthesurrogatekeycolumn
forthepreviousrecordinthechain,orthevaluethathasbeenspecifiedtousefor
thefirstrecordinthechain.

Input
:first_chain_value(int64)

Output
:surrogate_key_value(int64)

Examples
.Ifyouspecifythefollowingfunctioninthederivationfieldfor
aSKChaincolumninanSCDstage,theoutputcolumncontainsthevalue
ofthesurrogatekeyofthepreviousrecordinthechain,orthevalue
121060ifthisisthelastrowinthechain.
PrevSKChain(121060)
SaveInputRecord
Thisfunctionisusedtoimplementtheaggregatingofdataontheinputlinkofa
Transformerstage.YoucalltheSaveInputRecordfunctiontosaveacopyofthe
currentinputrowtoacachearea.Thefunctionreturnsthecountofrecordsinthe
cache,startingfrom1.YoucancallSaveInputRecordfromwithinthederivation
ofastagevariableintheTransformerstage.YoucancallSaveInputRecord
multipletimesforthesameinputrow.Thefirstcalladdstheinputrowtothe
cacheandeachsubsequentcalladdsaduplicateofthatsameinputrowintothe
cache.So,forexample,ifSaveInputRecordiscalledthreetimesforoneinput
record,thenthecachewillcontainthreerows,eachidenticaltotheoriginalinput
row.UsetheGetSavedInputRecordfunctiontoretrievetherowsthatyouhave
stored.

Input
:

Output
:cache_record_count(int64)

Examples
.Thefollowingexampleisthederivationofastagevariable
namedNumSavedRecordsinaTransformerstage:
NumSavedRecords:SaveInputRecord()