Table of Contents
AGGREGATE
Category: Transform / Basic
Description
This action calculates the aggregates for selected columns, grouped by another set of columns.
Available aggregations:
- Sum
- Count
- Count only distinct values
- Average
- Min
- Max
- Any
- Concatenate
Action settings
| Setting | Description |
|---|---|
| Calculate | Select the column in which the values will be aggregated. |
| Aggregation method | Select the aggregation method to be used on the values in the chosen column. |
| Group by | Select the column(s) in which the grouping of values will drive the aggregation. |
More than one aggregated column can be created by clicking the Aggregate by more columns option and selecting the additional column and aggregation to apply.
The output of this action is a dataset containing the "Group by" column, plus an additional column for each aggregation chosen.
Concatenate settings
| Setting | Description |
|---|---|
| Separator | Select the character (delimiter) that will appear between the concatenated values. Options: Comma, Semicolon, Tab, Pipe, Tilde, Space, Line break, or No separator. |
| Distinct | When checked, only unique values in the aggregated column will be included in the concatenation. |
Examples
Example 1: Find out the longest river on each continent.
Source table: The longest rivers in the world
| River | Length(km) | Continent |
|---|---|---|
| Nile | 6650 | Africa |
| Amazon | 6400 | South America |
| Yangtze | 6300 | Asia |
| Mississippi | 6275 | North America |
| Yenisei | 5539 | Asia |
| Yellow River | 5464 | Asia |
| Ob | 5410 | Asia |
| Paraná | 4880 | South America |
Action parameters:
Calculate: Length(km), Aggregation: Max
Group by: Continent
Result table:
| Continent | Max of Length(km) |
|---|---|
| Africa | 6650 |
| Asia | 6300 |
| North America | 6275 |
| South America | 6400 |
Example 2: Find out the longest river, and the number of rivers, on each continent.
Source table: Same source table as Example 1.
Action parameters:
Calculate: Length(km), Aggregation: Max
Calculate: River, Aggregation: Count
Group by: Continent
Result table:
| Continent | Max of Length(km) | Count of River |
|---|---|---|
| Africa | 6650 | 1 |
| Asia | 6300 | 3 |
| North America | 6275 | 1 |
| South America | 6400 | 2 |
Community examples
- Compare two Excel sheets with EasyMorph (Project; Module: Main; Group: Compare column names; Table: Actual columns list;
Action position: 3) - Generate CommaSeparated Fieldlist (Project; Module: Main; Group: Tab 1; Table: Table 1; Action position: 4)
- Which technique for grouping many columns of data together (i.e. 50+ columns) (Project; Module: Main; Group: Tab 1;
Table: Aggregate; Action position: 2) - Aggregate for +500 columns (Project; Module: Main; Group: Order preserved; Table: Book1.xlsx(2); Action position: 3)
- Collapsing multiple rows into one (Project; Module: Main; Group: Group 1; Table: Method 2; Action position: 2)
Shortcuts
Fast ways to create the action:
- Right-click on a column heading, and select "Aggregate", then select the aggregation method from the submenu.
- Click the drop-down arrow in a column heading and select "Aggregate", then select the aggregation method.