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
- •Variable
- •Window

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 an elastic mapping.

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.

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.

The transformation language provides the following conversion functions:

- •TO_BIGINT
- •TO_CHAR(Date)
- •TO_CHAR(Number)
- •TO_DATE
- •TO_DECIMAL
- •TO_FLOAT
- •TO_INTEGER

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

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.

The transformation language provides the following functions for data encryption, compression, encoding, and checksum:

- •AES_DECRYPT
- •AES_ENCRYPT
- •COMPRESS
- •CRC32
- •DEC_BASE64
- •DECOMPRESS
- •ENC_BASE64
- •MD5

The transformation language provides the following financial functions:

- •FV
- •NPER
- •PMT
- •PV
- •RATE

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%

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

The transformation language provides the following scientific functions:

- •COS
- •COSH
- •SIN
- •SINH
- •TAN
- •TANH

The transformation language provides the following special functions:

- •ABORT
- •DECODE
- •ERROR
- •IIF
- •SETCOUNTVARIABLE
- •SETMAXVARIABLE
- •SETMINVARIABLE
- •SETVARIABLE

You can nest other functions within special 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.

The transformation language provides the following test functions:

- •ISNULL
- •IS_DATE
- •IS_NUMBER
- •IS_SPACES

In an elastic mapping, 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.

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.

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 |

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:

- 1. COUNT (P1) produces one value for every row. COUNT returns the number of rows in the partition that have non-null values.
- 2. MEDIAN 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 )))

© Copyright Informatica LLC 2007, 2021