Transformations > Aggregator transformation > Aggregate fields
  

Aggregate fields

Use an aggregate field to define aggregate calculations.
When you configure an Aggregator transformation, create an aggregate field for the output of each calculation that you want to use in the data flow. You can use aggregate functions in aggregate fields. You can also use conditional clauses and nonaggregate functions.
Configure aggregate fields on the Aggregate tab of the Properties panel. When you configure an aggregate field, you define the field name, data type, precision, scale, and optional description. The description can contain up to 4000 characters. You also define the calculations that you want to perform.
When you configure aggregate fields, you can use variable fields for calculations that you want to use within the transformation. You can also include macros in aggregate and variable fields.

Aggregate functions

You can use aggregate functions in expressions in aggregate fields.
For more information about aggregate functions, see Function Reference.

Nested aggregate functions

A nested aggregate function is an aggregate function within another aggregate function.
For example, the following expression sums sales and returns the highest number:
MAX( SUM( SALES ))
You can include multiple single-level or multiple nested functions in different output fields in an Aggregator transformation. You cannot include both single-level and nested functions in an Aggregator transformation. You cannot nest aggregate functions in an elastic mapping.
When an Aggregator transformation contains a single-level function in any output field, you cannot use a nested function in any other field in that transformation. If you need to create both single-level and nested functions, create separate Aggregator transformations.

Conditional clauses

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 )