Combines rows if identical values occur multiple times in specified dimension columns. The numerical values in the remaining columns are combined using Average, Sum, Minimum, Maximum, or Number.
One or more numerical columns in a table are aggregated using none, one, or several dimension columns. In all rows with identical values in all dimension columns, the values in the columns to be aggregated are combined into one row based on an aggregation rule. That is, the result contains one row for each combination of dimension columns. This also applies if no columns are specified for aggregation. If no dimension column is specified, only a single row is created and all values in the columns to be aggregated are combined into a single value for each column. No rows are created for combinations that do not occur in the original table.
Parameters
The following parameters are available.
Action |
Result |
---|---|
Dimension column |
Name of dimension column. Source: Source table Data type: Date, Number, or Text The default value: {None} Specification: Optional |
Aggregation column |
Name of the column to be aggregated. Source: Aggregation column is transferred from the source table. Data type: Number The default value: {None} Specification: Optional |
Aggregation type |
Aggregation type for the column to be aggregated: Average value, Sum, Minimum, Maximum, Count, First row and Last row. The default value: Average value, if aggregation column selected. Specification: Mandatory, if aggregation column selected. |
Weighting |
If the aggregation type is Average value or Sum, a numerical column for weighting of the rows can be specified for each column to be aggregated. Specification: Optional |
Aggregation type
The following aggregation operations can be applied to the columns to be aggregated.
Data type |
Comparison operators |
Minimum |
Finds all rows that have a specific combination of values in the dimension columns and returns the lowest value that occurs in these rows in the column to be aggregated. |
Maximum |
Finds all rows that have a specific combination of values in the dimension columns and returns the highest value that occurs in these rows in the column to be aggregated. |
Average value |
Finds all rows that have a specific combination of values in the dimension columns and returns the average of the values in the column to be aggregated. For weighting purposes, an additional column can be specified for each source column, containing a weighting factor for each row. The weighting information is combined as a pair with the source column. |
Sum |
Finds all rows that have a specific combination of values in the dimension columns and returns the sum of the values in the column to be aggregated. For weighting, an additional column can be specified for each source column, containing a weighting factor for each row. The weighting information is combined as a pair with the source column. |
Count |
Finds all rows that have a specific combination of values in the dimension columns and returns the count of values in the column to be aggregated. |
First row |
Finds all rows that have a specific combination of values in the dimension columns and returns the value of the row with the lowest row index (according to the index column). |
Last row |
Finds all rows that have a specific combination of values in the dimension columns and returns the value of the row with the highest row index (according to the index column). |
At least one dimension or aggregation column, or both, must be set.
If no aggregation columns or dimension columns are specified, the incoming table remains unchanged.
Example
The following table is to be aggregated based on the Dim 1 and Dim 2 columns. The sum is to be calculated for the Values 1 column and the average for the Values 2 column. The Weight (values 2) column is used for weighting the Values 2 column one row at a time.
Dim 1 |
Dim 2 |
Values 1 |
Values 2 |
Weight (values 2) |
A |
X |
1 |
2 |
3 |
B |
Y |
2 |
4 |
4 |
C |
Z |
5 |
6 |
3 |
A |
X |
7 |
8 |
4 |
B |
Y |
9 |
10 |
3 |
C |
Z |
11 |
12 |
4 |
Result
Dim 1 |
Dim 2 |
Sum (values 1) |
Average (values 2) |
A |
X |
8 (1+7) |
5,43 (2*3 + 8*4)/(3+4) |
B |
Y |
12 (3+9) |
6,57 (4*4 + 10*3)/(4+3) |
C |
Z |
16 (5+11) |
9,43 (6*3 + 12*4)/(3+4) |