- opening/closing stock values
- identifying minimum/maximum temperature over time
- counting discrete product values
Cumulating values within a single column in an Integrator transform has always been possible, but required a custom Groovy script to achieve the right outcome. The ColumnAggregation function simplifies these types of calculations.
Function editor
The function is defined in the Function editor of a Field Transform. This function can have maximum 2 inputs (columns):
Column 1 : Value to aggregate on (mandatory)
Column 2 : A condition that can reset the aggregation (optional)
The screenshot below shows the Function editor with a single input, sales and aggregation set to sum.
The output column shows an aggregated value of all rows from an input column, up to the current row.
Examples
You can see column aggregation in action in the Integrator sample project sampleFieldTransform. Use the screenshot below as a guide for navigating to this project.
This sample project has two transforms that use column aggregation: one with a single input column and one with two input columns. In each case, the data source is an extract, E_Products, which contains product names (productName) and products sold (sales).
Single input column
The example transformT_ColumnAggregationSingleInput has a single input column, sales. The transform consists of 4 functions, each of which performs a different aggregation on the source column:
The data preview shows the columns that result from the aggregation on the sales column.
Two input columns
The example T_ColumnAggregationTwoInputs has two input columns, sales and productName. As in the previous example, the transform consists of 4 functions, each of which performs a different aggregation on the source column:
However, in this case there are two input columns defined in the Function editor, sales and productName. The aggregations will be grouped by the second input (in this case, productName).
The resulting columns show the figures from the sales column aggregated with respect to the values in the productName column. The productName values "reset" the aggregation in the respective results columns.
Updated September 27, 2022