Transformation - Aggregator

Transformation - Aggregator
Aggregator is an active transformation. Output from the aggregator can be different from input. Designer allows aggregator functions only in this transfromation. Following types of function can be used

1. MIN
2. MAX
3. AVG
4. COUNT
5. FIRST
6. LAST
7. MEDIAN
8. PERCENTILE
9. STDDEV
10. SUM
11. VARIANCE

Along with these aggregate function, you can use other row level functions such as IIF, DECODE etc.

In Aggregator transformation, at least one port has to be selected as group by column. By default, aggregator will return the last value for a port ( if there are more than one record for group by column). Aggregator will also sort the data in ASC order on group by port.

NOTE: If primary column of the source is used in group by port, then aggregator will work as sorter transformation.

Nested Aggregate ports can not be used in Aggregator. Means, you can not get the count(*) in one port and use this value in other Aggregator port. This will invalidate the mapping.

Aggregtor has a property "SORTED INPUT". If you check this property, then aggregator assumes that data is coming in sorted order ( on group by ports). If not, at run time session will fail. Sorted Input improves the aggregator performance.