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)
There are two basic aggregation types:
It counts the number of rows in each group.
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.
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:
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:
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:
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.
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.
Here, we can see the Car sales like Max sales, Min sales, Mean sales and Sum sales.