Differences

This shows you the differences between two versions of the page.

--- transformations:aggregate [2020/02/10 11:23] – dmitry
+++ transformations:aggregate [2021/09/12 11:25] (current) – [Action settings] craigt
@@ Line 1: / Line 1: @@
-===== Aggregate =====
+{{ transformations:AggregateAction.png}}
+====== AGGREGATE ======
+Category: Transform / Basic\\
-This transformation calculates the aggregates for selected columns grouped by another set of columns.
+\\
+=====Description=====
+This action calculates the aggregates for selected columns, grouped by another set of columns.
 Available aggregations:
@@ Line 11: / Line 15: @@
   * Max
   * Any
+  * Concatenate
-EXAMPLE
+\\
+=====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.
-**Source table:** The longest rivers in the world
+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.|
-^  River  ^  Length(km)  ^  Continent  ^
+\\
-| Nile  |  6650  |  Africa  |
-| Amazon  |  6400  |  South America  |
+=====Examples=====
-| Yangtze  |  6300  |  Asia  |
-| Mississippi  |  6275  |  North America  |
+**Example 1:** Find out the longest river on each continent.
-| Yenisei  |  5539  |  Asia  |
-| Yellow River  |  5464  |  Asia  |
+**Source table:** The longest rivers in the world
-| Ob  |  5410  |  Asia  |
-| Paraná  |  4880  |  South America  |
-**Objective:** Find out the longest river on each continent.
+^ 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|
-**Transformation parameters:**
+\\
+\\
+**Example 2:** Find out the longest river, and the number of rivers, on each continent.
-  * Calculate: Length(km)
+**Source table:** Same source table as Example 1.\\
-  * Aggregation: Max
+\\
-  * Group by: Continent
+**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|
-**Output table:**
+\\
+====Community examples====
+  * [[https://community.easymorph.com/t//675/5|Compare two Excel sheets with EasyMorph]] ([[https://community.easymorph.com/uploads/short-url/xQLWtAogCqcMrglyfXLKmrAF7aR.morph|Project]]; Module: //Main//; Group: //Compare column names//; Table: //Actual columns list//;\\ Action position: //3//)
+  * [[https://community.easymorph.com/t//1357/2|Generate CommaSeparated Fieldlist]] ([[https://community.easymorph.com/uploads/short-url/7BiLOXdlQxZWOEOqIwfASApQern.morph|Project]]; Module: //Main//; Group: //Tab 1//; Table: //Table 1//; Action position: //4//)
+  * [[https://community.easymorph.com/t//1652/2|Which technique for grouping many columns of data together (i.e. 50+ columns)]] ([[https://community.easymorph.com/uploads/short-url/ub3zV5WsGF4rWqJQMb7oEtPReqy.morph|Project]]; Module: //Main//; Group: //Tab 1//;\\ Table: //Aggregate//; Action position: //2//)
+  * [[https://community.easymorph.com/t//1778/2|Aggregate for +500 columns]] ([[https://community.easymorph.com/uploads/short-url/8w0FN8IXxRdEIORedS9u8DKqhfA.morph|Project]]; Module: //Main//; Group: //Order preserved//; Table: //Book1.xlsx(2)//; Action position: //3//)
+  * [[https://community.easymorph.com/t//2321/4|Collapsing multiple rows into one]] ([[https://community.easymorph.com/uploads/short-url/j6CBEKHa76q1v9oQqui4UPWN7ro.morph|Project]]; Module: //Main//; Group: //Group 1//; Table: //Method 2//; Action position: //2//)
-^  River  ^  Continent  ^  Max of Length(km)  ^
+\\
-| Nile  |  Africa  |  6650  |
+=====Shortcuts=====
-| Yangtze  |  Asia  |  6300  |
+Fast ways to create the action:\\
-| Mississippi  |  North America  |  6275  |
+  *Right-click on a column heading, and select "Aggregate", then select the aggregation method from the submenu.
-| Amazon  |  South America  |  6400  |
+  *Click the drop-down arrow in a column heading and select "Aggregate", then select the aggregation method.
-=== Notes ===
-The "Any" aggregation picks only 1 arbitrary (random) value from a group of values and discards the rest.
+\\
+=====See also=====
+  * [[transformations:addcolumn|Calculate new column(s)]]
+  * [[transformations:replacecolumns|Modify columns(s)]]
+  * [[transformations:Rule|Rule]]
-The "Any" aggregation is typically used for non-numeric values, where all values in a group are known to be the same. E.g. "Any" applied to "ABC", "ABC", "ABC" will return "ABC".
-=== See also ===
-  * [[transformations:runningtotal|Running total]]
-  * [[transformations:subtotal|Total / subtotals]]
-  * [[transformations:breakdown|Break down]]