This transform aggregates the data according to a set of key columns and different aggregation functions.
Settings
The first step is to choose your data source in the respective field.
Then, in the Target table, go to the "Input" field and select the columns you wish to use as keys for the aggregation (see image below).
For example, if you choose "Regions" as a key column, all rows presenting the same value in this column in the source table (e.g. Germany) will be aggregated into a single row in the new table.
You may choose more than one key column, e.g. "Regions" and "Years". In this case, all rows presenting the same values in both key columns will be aggregated into a single row in the new table (for example, Germany 2018), and henceforth.
Furthermore, you can either keep the original names of the source columns, or define new ones by filling in the "Field name" area on the left-hand side of the Target table.1
Next, go to the Measures table. Specify an aggregation function in the "Aggregation" field, and define the input value in the "Input" field.
For instance, you can choose the "sum" function and define "Value" as input. In this case, all rows having an identical value in the key column will be grouped into a single row, and their Values will be summed and presented in a "sum" column in the new table. See the example below for a more detailed explanation.
You can also define a different name for the header of the aggregation columns - just fill in the "Measure" field on the left-hand side. If this field is left empty, the function name will be the header of the aggregation column.2
It is possible to define the measure type as text, numeric (double), or integer. (This is also an option for the TableNormalization and TableDenormalization transforms.)
You can also create output columns through regular expressions. Data delimiter options are
- comma (default)
- Tab as data delimiter: \t
- For blank value (" ") as data delimiter: #space
- semicolon
After the changes, click Save.
Consider you have the data below (just a sample here):
You wish to aggregate the data using the "Regions" and "Years" columns as keys, and sum their values. That means you need the "sum" function.
For this, select the key columns in the "Input" field in the Target table. Next, in the Measures table, select the "sum" function in the "Aggregation" field, and define "Value" as the "Input".
Save the changes.
In the data preview, you can see that all rows showing a same country and a same year in the source table were grouped into a single row in the new table, and their values were summed in the "Sum" column. Check "Germany", for example:
The same procedure is valid for all aggregation functions, which are presented in the table below.
Aggregation functions
The following aggregation functions are available for transforms TableAggregation, TableNormalization, and TableDenormalization:
| sum | Sum |
| min | Minimum |
| max | Maximum |
| avg | Average |
| first | The first data entry for one target position |
| last | The last data entry for one target position |
| count | Number of data entries written on one target position |
| count_distinct |
Number of distinct data entries written on one target position |
| group_concat | Concatenates all data entries with separator “,” while maintaining order of source rows |
| concat_distinct | Concatenates all distinct data entries with separator “,” (i.e. remove duplicate values); results are in ascending order |
| selectivity | Estimates the selectivity (0-100) of a value. The value is defined as (100 * distinctCount / rowCount). |
| var_samp | The sample variance (square of the sample standard deviation) |
| stddev_samp | The sample standard deviation |
| var_pop | The population variance (square of the population standard deviation) |
| stddev_pop | The population standard deviation |
| none | No aggregation occurs; the last data set is written (overwriting). |
| Column include pattern | Regular expression for column names to be included as output columns of the transform. |
| Column exclude pattern | Regular expression for column names to be excluded as output columns of the transform. |
| Measure include pattern | Regular expression for measure names to be included as output columns of the transform. |
| Measure exclude pattern | Regular expression for measure names to be excluded as output columns of the transform. |
| Measure aggregation | The aggregation function used for all measures which are added dynamically via the Measure include or exclude pattern. |
| Keep source order | If set, the order of output rows respects the order in the source with first appearance of a particular key. If not set the order is arbitrary but the performance might be better. |
| Use caching |
When checked (true), caching will be used; when not checked (false), caching will not be used. See Caching in Extracts and Transforms. |
Special case: distinct rows
The distinct rows of a source, i.e. removing of all duplicate rows, can be achieved with this transform by setting "Column include pattern" to "." (i.e. the regular expression for accept all) and no measures. In order to maintain the order of rows the option "Keep source order" has to be set.
1. This is the case when the input is coming dynamically from the source or transform. When the target is manually defined as constant, the name "constant" is chosen instead of the column name in the "Input" field. Therefore, adding more than one constant column results in error, because there would be two columns with the name "constant".
2. When the "Measure" field is left empty, the name of the "Input" field is chosen as column name. However, more than one empty "Measure" field with the same "Input" value results in error, because there would be two columns with the same name.
Updated November 4, 2024