AGGREGATE

Category: Transform / Basic

Description

This action calculates the aggregates for selected columns, grouped by another set of columns.

Use cases

Available aggregations:

Sum: generates the total of values.
Count: generates the count of values.
Count only distinct values: generates the count of unique values.
Average: generates the average of values.
Min: generates the minimum of all values.
Max: generates the maximum of all values.
Any: returns the first value in the selected column.
Concatenate: combines all values into a single string, delimited by the selected character.

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

Determine the longest river on each continent.

Before (source table)

River	Length(km)	Continent
Amazon	6400	South America
Yangtze	6300	Asia
Yenisei	5539	Asia
Mississippi	6275	North America
Ob	5410	Asia
Paraná	4880	South America

After (result table)

Continent	Length(km)
Asia	6300
North America	6275
South America	6400

Action parameters:

Calculate: Length(km), Aggregation: Max
Group by: Continent

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.

EasyMorph Help

Table of Contents