You can use aggregate functions in mapping tasks. You can use all other functions in mapping tasks and synchronization tasks.
The transformation language provides the following function categories:
•Aggregate
•Conversion
•Data Cleansing
•Date
•Encoding
•Financial
•Horizontal Expansion
•Numerical
•Scientific
•Special
•String
•Test
•Window
Aggregate functions
Aggregate functions return summary values for non-null values in selected fields.
With aggregate functions you can complete the following tasks:
•Calculate a single value for all rows in a group.
•Return a single value for each group in an Aggregator object.
•Apply filters to calculate values for specific rows in the selected fields.
•Use operators to perform arithmetic within the function.
•Calculate two or more aggregate values derived from the same source columns in a single pass.
Use aggregate functions in mapping tasks only.
The transformation language includes the following aggregate functions:
•AVG
•COUNT
•FIRST
•LAST
•MAX (Date)
•MAX (Number)
•MAX (String)
•MEDIAN
•MIN (Date)
•MIN (Number)
•MIN (String)
•PERCENTILE
•STDDEV
•SUM
•VARIANCE
Use aggregate functions in Aggregator objects only. You can nest only one aggregate function within another aggregate function. Data Integration evaluates the innermost aggregate function expression and uses the result to evaluate the outer aggregate function expression. You cannot nest aggregate functions in advanced mode.
You can set up an Aggregator object that groups by ID and nests two aggregate functions as follows:
SUM( AVG( earnings ) )
where the dataset contains the following values:
ID
EARNINGS
1
32
1
45
1
100
2
65
2
75
2
76
3
21
3
45
3
99
The return value is 186. Data Integration groups by ID, evaluates the AVG expression, and returns three values. Then it adds the values with the SUM function to get the result.
Filter conditions
Use a filter condition to limit the rows returned in a search.
A filter limits the rows returned in a search. You can apply a filter condition to all aggregate functions and to CUME, MOVINGAVG, and MOVINGSUM. The filter condition must evaluate to TRUE, FALSE, or NULL. If the filter condition evaluates to NULL or FALSE, Data Integration does not select the row.
You can enter any valid transformation expression. For example, the following expression calculates the median salary for all employees who make more than $50,000:
MEDIAN( SALARY, SALARY > 50000 )
You can also use other numeric values as the filter condition. For example, you can enter the following as the complete syntax for the MEDIAN function, including a numeric field:
MEDIAN( PRICE, QUANTITY > 0 )
In all cases, Data Integration rounds a decimal value to an integer (for example, 1.5 to 2, 1.2 to 1, 0.35 to 0) for the filter condition. If the value rounds to 0, the filter condition returns FALSE. If you do not want to round up a value, use the TRUNC function to truncate the value to an integer:
MEDIAN( PRICE, TRUNC( QUANTITY ) > 0 )
If you omit the filter condition, the function selects all rows in the field.
Conversion functions
The transformation language provides the following conversion functions:
•TO_BIGINT
•TO_CHAR(Date)
•TO_CHAR(Number)
•TO_DATE
•TO_DECIMAL
•TO_FLOAT
•TO_INTEGER
Data cleansing functions
The transformation language provides a group of functions to eliminate data errors. You can complete the following tasks with data cleansing functions:
•Test source values.
•Convert the datatype of an source value.
•Trim string values.
•Replace characters in a string.
•Encode strings.
•Match patterns in regular expressions.
The transformation language provides the following data cleansing functions:
•BETWEEN
•GREATEST
•IN
•INSTR
•IS_DATE
•IS_NUMBER
•IS_SPACES
•ISNULL
•LEAST
•LTRIM
•METAPHONE
•REG_EXTRACT
•REG_MATCH
•REG_REPLACE
•REPLACECHR
•REPLACESTR
•RTRIM
•SOUNDEX
•SUBSTR
•TO_BIGINT
•TO_CHAR
•TO_DATE
•TO_DECIMAL
•TO_FLOAT
•TO_INTEGER
Date functions
The transformation language provides a group of date functions to round, truncate, or compare dates, extract one part of a date, or perform arithmetic on a date.
You can pass any value with a date datatype to any of the date functions. However, if you want to pass a string to a date function, you must first use the TO_DATE function to convert it to a transformation Date/Time datatype.
The transformation language provides the following date functions:
•ADD_TO_DATE
•DATE_COMPARE
•DATE_DIFF
•GET_DATE_PART
•LAST_DAY
•MAKE_DATE_TIME
•ROUND
•SET_DATE_PART
•SYSTIMESTAMP
•TRUNC
Several of the date functions include a format argument. You must specify one of the transformation language format strings for this argument. Date format strings are not internationalized.
The Date/Time transformation datatype does not support milliseconds. Therefore, if you pass a date with milliseconds, Data Integration truncates the millisecond portion of the date.
Encoding functions
The transformation language provides the following functions for data encoding, encryption, compression, and checksum:
•AES_DECRYPT
•AES_ENCRYPT
•AES_GCM_DECRYPT
•AES_GCM_ENCRYPT
•COMPRESS
•CRC32
•DEC_BASE64
•DECOMPRESS
•ENC_BASE64
•MD5
•SHA256
Financial functions
The transformation language provides the following financial functions:
•FV
•NPER
•PMT
•PV
•RATE
Horizontal expansion functions
Use a horizontal expansion function to create a horizontal macro expression.
Horizontal expansion functions use the following naming convention: %OPR_<function_type>%.
Horizontal expansion functions use square brackets ( [ ] ) instead of parentheses.
The transformation language provides the following horizontal expansion functions:
•%OPR_CONCAT%
•%OPR_CONCATDELIM%
•%OPR_IIF%
•%OPR_SUM%
Numeric functions
The transformation language provides the following numeric functions:
•ABS
•CEIL
•CONV
•CUME
•EXP
•FLOOR
•LN
•LOG
•MOD
•MOVINGAVG
•MOVINGSUM
•POWER
•RAND
•ROUND
•SIGN
•SQRT
•TRUNC
Scientific functions
The transformation language provides the following scientific functions:
•COS
•COSH
•SIN
•SINH
•TAN
•TANH
Special functions
The transformation language provides the following special functions:
•ABORT
•DECODE
•ERROR
•IIF
•SETCOUNTVARIABLE
•SETMAXVARIABLE
•SETMINVARIABLE
•SETVARIABLE
You can nest other functions within special functions.
String functions
The transformation language provides the following string functions:
•ASCII
•CHOOSE
•CHR
•CHRCODE
•CONCAT
•INDEXOF
•INITCAP
•INSTR
•LENGTH
•LOWER
•LPAD
•LTRIM
•REPLACECHR
•REPLACESTR
•REVERSE
•RPAD
•RTRIM
•SUBSTR
•UPPER
To evaluate character data, the string functions LOWER, UPPER, and INITCAP use the code page of the Secure Agent that runs the task.
Test functions
The transformation language provides the following test functions:
•ISNULL
•IS_DATE
•IS_NUMBER
•IS_SPACES
Window functions
In advanced mode, the transformation language includes a group of window functions that perform calculations on a set of rows that are related to the current row. The functions calculate a single return value for every input row.
The transformation language provides the following window functions:
•LAG
•LEAD
You can use window functions in an Expression transformation after you configure the window properties. If you configure window properties, you can also use the aggregate functions as window functions. As window functions, the aggregate functions do not group rows into a single output row but return an output value for each individual row.
Aggregate functions as window functions
In addition to the LEAD and the LAG functions, you can use aggregate functions as window functions. When you use an aggregate function like SUM or AVG as a window function, you can perform running calculations. Window functions are more flexible than stateful functions because you can set a specific end offset.
To use an aggregate function as a window function, you must define a frame in the window properties to limit the scope of the calculation. The aggregate function performs a calculation across the frame and produces a single value for each row.
Example
You are a lumber salesperson who sold different quantities of wood over the past two years. You want to calculate a running total of sales quantities.
The following table lists each sale ID, the date, and the quantity sold:
Sale_ID
Date
Quantity
30001
2016-08-02
10
10001
2016-12-24
10
10005
2016-12-24
30
40001
2017-01-09
40
10006
2017-01-18
10
20001
2017-02-12
20
A SUM function adds all the values and returns one output value. To get a running total for each row, you can define a frame for the function boundaries.
You configure the following properties on the Window tab:
•Start offset: All Rows Preceding
•End offset: 0
•Order Key: Date Ascending
You define the following aggregate function:
SUM (Quantity)
SUM adds the quantity in the current row to the quantities in all the rows preceding the current row. The function returns a running total for each row.
The following table lists a running sum for each date:
Sale_ID
Date
Quantity
Total
30001
2016-08-02
10
10
10001
2016-12-24
10
20
10005
2016-12-24
30
50
40001
2017-01-09
40
90
10006
2017-01-18
10
100
20001
2017-02-12
20
120
Nested aggregate functions as window functions
A nested aggregate function in a window function performs a separate calculation for each partition.
When you include nested aggregate functions in an Expression transformation and configure the transformation for window functions, the function performs the calculation separately for each partition.
You partition the data by P2 and specify a frame of All Preceding Rows and All Following Rows. The window functions perform the following calculations:
1COUNT (P1) produces one value for every row. COUNT returns the number of rows in the partition that have non-null values.
2MEDIAN of that value produces the median of a window of values generated by COUNT.
The window functions produce the following outputs:
P1
P2
Output
10
1
3
7
1
3
12
1
3
11
2
4
13
2
4
8
2
4
10
2
4
You can nest aggregate functions with multiple window functions. For example:
LAG ( LEAD( MAX( FIRST ( p1 )))
Note: You can nest multiple window functions LEAD and LAG, but you cannot nest more than one aggregate function within another aggregate function.