Aggregator Stage Example

Aggregator stage is one of a processing stage in Datastage and is used to perform aggregate functions such as MAX, SUM, COUNT etc by grouping and summary operations.
We need to specify:

– One or more key columns that define the aggregation units (or groups)

– Columns to be aggregated

– Aggregation functions include, among many others:
(Count, sum, max/min/range)

Aggregation Types:

There are two basic aggregation types:
Count rows:
It counts the number of rows in each group.
Calculation:
It allows us to select an input column and perform calculations on.
Like Sum, Count, Min/Max, Mean, Missing value count, Non-missing value count, percent coefficient of variation.

Example:

In Below example we are having CarSales.txt as input which contains Car sales details on which we are going to apply some aggregation functions using Aggregator stage.

Design the job similar to as shown below:

 

1 - Aggregator Stage Example

Input Data:

input - Aggregator Stage Example

Open aggregator stage properties by double click on it or right clickàProperties, under StageàProperties tab, select the Group key column and Aggregation functions as below:

2 - Aggregator Stage Example

Open Properties window of the Aggregator stage by double click on it or Right clickàselect Properties from drop down. We can see the input columns as below:

5 - Aggregator Stage Example

Under Output à Mapping tab, select the columns from input drag and drop those to output similar to below snapshot to perform mapping of columns from input to output.

3 - Aggregator Stage Example

Save the job and compile and run it. After successful job run we can find 3 rows have been inserted to the target dataset out of 12 records. 

output - Aggregator Stage Example

Output Data:

Here, we can see the Car sales like Max sales, Min sales, Mean sales and Sum sales. 

4 - Aggregator Stage Example