Aggregate step (DataStage®)
Perform hierarchical aggregations on the items in a list.
In the following example, the aggregate function, average, is computed for the salary of all
employees in each department. The departmentID is used as the key to group Employee items and the
averageSalary is computed for each group. The result list contains an item for each distinct departmentID/grouping.
Company[]
Employee[]
departmentID
salary
result[]
keys
departmentID
aggregate
averageSalary
- List to Aggregate
- Specify the list that contains the elements that will be iterated on.
- Scope
- Defines the scope of the Aggregate function and determines the location of the output of the Aggregate step. The scope must always be the parent node of the list that is selected in the List to Aggregate field.
- Aggregation Item and Aggregation Function
- Select an item and the function to use for the aggregation. The function that you select must be
applicable to the data type of the selected item. The following table describes the available
aggregation functions.
Table 1. Aggregation functions Function Description Average Calculates the average value in the list. Count Counts the elements in the list. Ignore null values. Concatenate Concatenates all strings in the list, starting with the first element. First Selects first value in the list. Last Selects last value in the list. Maximum Selects the maximum value in the list. If the list contains Boolean strings, True is greater than False. Minimum Selects the minimum value in the list. If the list contains Boolean strings, False is less than True. Sum Calculates the sum of all of the values in the list. Variance Calculates the variance value of all of the values in the list. - Aggregation Keys
- Specify aggregation keys to produce multiple aggregation rows for each unique key value.