Differences

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

--- transformations:merge [2018/04/30 09:44] – dmitry
+++ transformations:merge [2024/04/11 23:55] (current) – add inner join craigt
@@ Line 1: / Line 1: @@
-===== Merge another table =====
+{{ transformations:MergeAction.png}}
+====== MERGE ANOTHER TABLE ======
+Category: Transform / Basic\\
-This transformation appends one or more columns from another table where certain columns in both tables match.
+\\
+=====Description=====
+This action appends one or more columns from another table where the values in certain columns in both tables match. The action is similar to the SQL join.
-There are three possible merge modes:
+There are four possible merge modes:\\
+====Lookup Mode====
+Retrieve the //first// matching combination from the other table. Any subsequent combinations are ignored.\\
+\\
+====Left Join Mode====
+Retrieve //all// matching combinations from the other table. This mode can increase the number of rows in the current table as rows are duplicated to hold any multiple matches from the second dataset.\\
+\\
+====Full Join Mode====
+Include all combinations of keys from both tables. Similar to the full outer join in SQL.\\
+\\
+====Inner Join Mode====
+Include only matching rows from both tables.\\
-  * Lookup Mode -- get **first** matching combination from the other table. If there are other combinations they are ignored.
+\\
-  * Left Join Mode -- get **all** matching combinations from the other table. It can increase number or rows in the current table.
+=====Use cases=====
-  * Full Join Mode -- include all combinations of keys from both tables. Equivalent to the full outer join in SQL.
+  * (Lookup Mode) Look up replacement values for common misspellings or abbreviations.
+  * (Left Join Mode) Blend in additional data from other tables into the primary dataset.
+  * (Full Join Mode) Combine two entire datasets to be cleaned and consolidated downstream.
+  * (Inner Join Mode) Connect only rows from both tables where the values in the selected fields match.
-EXAMPLE
+\\
+=====Action settings=====
+^Setting^Description^
+|Add columns from table|Select table that will return columns to the current dataset where values match.|
+|Where all these columns match|Select the columns within the current dataset (left drop-down) and the second dataset (right drop-down) where\\ values will match.  To define multi-columnar matches, use the //Add more matching columns// to select additional matching columns in each dataset.|
+|Merge mode|Options:\\  //Lookup// (for each row in this table, get any one matching row from the second table and ignore other matches),\\ //Left join// (for each row in this table add all matching rows from the second table),\\ //Full join// (add matching and non-matching rows from both tables)\\ //Inner join// (add rows that match in both tables).|
+|Return columns|From the list, select the columns from the second dataset that will be appended to the current dataset.|
+The reference to the second table will appear as a dotted line connecting this action to the second dataset in the application window.
-**Table 1:** The longest rivers in the world
+\\
+=====Remarks=====
+Matching is case-sensitive.
-^  River  ^  Length (km)  ^  Continent  ^
+Values must be identical (equal) in order to be matched. If another matching mode is needed (e.g. "Starts with") use the [[transformations:match|Match]] action first to do the matching.
-| Nile  |  6650  |  Africa  |
-| Amazon  |  6400  |  South America  |
-| Yangtze  |  6300  |  Asia  |
-| Mississippi  |  6275  |  North America  |
-**Table 2:** The outflow
+For an inner join use the [[transformations:keepremovematching|Keep/remove matching]] action before merging to keep only the overlapping subset of values in the matching columns.
-^  River  ^  Outflow  ^
-| Nile  |  Mediterranean  |
-| Amazon  |  Atlantic Ocean  |
-| Yangtze  |  East China Sea  |
-| Mississippi  |  Gulf of Mexico  |
+\\
+=====Examples=====
-**Objective:** Merge the "Outflow" column of Table 2 into Table 1
-**Transformation:**
+**Example:** Merge the "Outflow" column of Table 2 into Table 1 based on matching values in the "Rivers" columns in both tables.\\
-  * Add columns from table: Table 2
+**Table 1:** The longest rivers in the world\\
-  * Column: Outflow
-  * Merge mode: Add more rows to this table and all matching rows from Table 2 (i.e. do left join)
-**Result:**
+^River  ^Length (km)  ^Continent  ^
+| Nile  |  6650|Africa  |
+| Amazon  |  6400|South America  |
+| Yangtze  |  6300|Asia  |
+| Mississippi  |  6275|North America  |
+\\
+**Table 2:** The outflow\\
-^  River  ^  Length (km)  ^  Continent  ^  Outflow  ^
+^River  ^Outflow  ^
-| Nile  |  6650  |  Africa  |  Mediterranean  |
+| Nile  |Mediterranean  |
-| Amazon  |  6400  |  South America  |  Atlantic Ocean  |
+| Amazon  |Atlantic Ocean  |
-| Yangtze  |  6300  |  Asia  |  East China Sea  |
+| Yangtze  |East China Sea  |
-| Mississippi  |  6275  |  North America  |  Gulf of Mexico  |
+| Mississippi  |Gulf of Mexico  |
+\\
+**Action parameters:**
-=== Limitations ===
+> Add columns from table: Table 2
-The resulting table after Merge can't have more than 134,217,728 rows.
+> Where values in Table 1 "River" column match values in Table 2 "River" column
+> Merge mode: Left join (add all matching rows from Table 2 to Table 1)
+> Return column: Outflow
-=== See also ===
+\\
+**Result table:**\\
+^River  ^Length (km)  ^Continent  ^Outflow  ^
+| Nile  |  6650|Africa  |Mediterranean  |
+| Amazon  |  6400|South America  |Atlantic Ocean  |
+| Yangtze  |  6300|Asia  |East China Sea  |
+| Mississippi  |  6275|North America  |Gulf of Mexico  |
+\\
+====Community examples====
+  * [[https://community.easymorph.com/t//1273/8|Split Excel spreadsheets into multiple spreadsheets]] ([[https://community.easymorph.com/uploads/short-url/klG62NjaDDRQ2VXvo1xtFZJUYjK.morph|Project]]; Module: //Main//; Group: //Tab 1//; Table: //Spreadsheet A.xlsx//;\\ Action position: //2//)
+  * [[https://community.easymorph.com/t//1489/3|How to calculate YoY change using self-join]] ([[https://community.easymorph.com/uploads/short-url/40p81AvBk4V0GKWU3IHpy1evo2K.morph|Project]]; Module: //Main//; Group: //Self-join//; Table: //This year//; Action position: //2//)
+  * [[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: //Table 2//; Action position: //4//)
+  * [[https://community.easymorph.com/t//2041/6|Join 2 tables with multi criterions equal and not equals in where]] ([[https://community.easymorph.com/uploads/short-url/kNkqMsDYMJjzjiN4C5g8O4g3YlA.morph|Project]]; Module: //Main//; Group: //Tab 1//; Table: //Join//; Action position: //2//)
+  * [[https://community.easymorph.com/t//2421/8|“Merge another table” action - merge all columns (independently of the column name)]] ([[https://community.easymorph.com/uploads/short-url/jHsMLkfW9YZVmGYNYtayhOt1VkJ.morph|Project]]; Module: //Merge//; Group: //Merge//;\\ Table: //Merge tables//; Action position: //2//)
+\\
+=====See also=====
   * [[transformations:append|Append another table]]
+  * [[transformations:lookup|Lookup]]
+  * [[transformations:intervalmerge|Interval merge]]