Aggregate Expressions
Configure aggregate expressions in the variable ports or the output ports of an Aggregator transformation. You cannot enter an expression if the port is an input port, a pass-through port, or a dynamic port.
An aggregate expression can include conditional clauses and functions that are not aggregate functions. The aggregate function can also include an aggregate function nested within another aggregate function, such as:
MAX( COUNT( ITEM ))
The aggregate expression result varies based on the group by ports in the transformation. For example, the following aggregate expression finds the total quantity of items sold:
SUM( QUANTITY )
However, if you use the same expression, and you group by the ITEM port, the Data Integration Service returns the total quantity by item.
You can create an aggregate expression in any output port and use multiple aggregate ports in a transformation.
Aggregate Functions
Configure aggregate functions within an Aggregator transformation. You can nest one aggregate function within another aggregate function.
The transformation language includes the following aggregate functions:
- •AVG
- •COUNT
- •FIRST
- •LAST
- •MAX
- •MEDIAN
- •MIN
- •PERCENTILE
- •STDDEV
- •SUM
- •VARIANCE
If you use a port in an expression in the Aggregator transformation but you do not use the port within an aggregate function, the Data Integration Service uses the last row in the port to process the expression.
For example, you create an Aggregator transformation that contains the ports COMMISSIONS and SALARY. The port SALARY is a group-by port.
You might use the following expression in an output port:
SUM(COMMISSIONS)
The Data Integration Service processes the Aggregator function and returns the sum of the values in the port COMMISSIONS in the output port.
You might modify the expression to the following expression:
SUM(COMMISSIONS) + COMMISSIONS
To process the expression, the Data Integration Service returns the sum of the values in the port COMMISSIONS and adds the value of the last row in the port COMMISSIONS to the return value in the output port.
For a different output port, you might use the following expression:
SUM(COMMISSIONS) + SALARY
To process the expression, the Data Integration Service returns the sum of the values in the port COMMISSIONS and adds the value in the last row of the port SALARY to the return value in the output port. Note that the values in each row of the port SALARY are the same because the SALARY port is a group-by port.
Nested Aggregate Functions
You can include multiple single-level or multiple nested functions in different output ports in an Aggregator transformation.
You cannot include both single-level and nested functions in an Aggregator transformation. Therefore, if an Aggregator transformation contains a single-level function in any output port, you cannot use a nested function in any other port in that transformation. When you include single-level and nested functions in the same Aggregator transformation, the Developer tool marks the mapping or mapplet invalid. If you need to create both single-level and nested functions, create separate Aggregator transformations.
Conditional Clauses in Aggregate Expressions
Use conditional clauses in the aggregate expression to reduce the number of rows used in the aggregation. The conditional clause can be any clause that evaluates to TRUE or FALSE.
For example, use the following expression to calculate the total commissions of employees who exceeded their quarterly quota:
SUM( COMMISSION, COMMISSION > QUOTA )