EasyMorph Help

User Tools

Site Tools

You are here: start » transformations » merge
transformations:merge

Differences

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

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
transformations:merge [2021/04/05 00:33] – craigttransformations:merge [2024/04/11 23:55] (current) – add inner join craigt
Line 5: Line 5:
 \\  \\ 
 =====Description===== =====Description=====
-This action appends one or more columns from another table where the values in certain columns in both tables match.+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==== ====Lookup Mode====
Line 16: Line 16:
 \\  \\ 
 ====Full Join Mode==== ====Full Join Mode====
-Include all combinations of keys from both tables. Equivalent to the full outer join in SQL.  This mode could potentially return very large datasets.\\ +Include all combinations of keys from both tables. Similar to the full outer join in SQL.\\
 \\  \\ 
-=====Limitations===== +====Inner Join Mode==== 
-The resulting table after Merge can't have more than 134,217,728 rows.\\+Include only matching rows from both tables.\\
  
 \\ \\
Line 27: Line 26:
   * (Left Join Mode) Blend in additional data from other tables into the primary dataset.   * (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.   * (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.
  
 \\  \\ 
Line 33: Line 33:
 |Add columns from table|Select table that will return columns to the current dataset where values match.| |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.| |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).|+|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.| |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. The reference to the second table will appear as a dotted line connecting this action to the second dataset in the application window.
 +
 +\\ 
 +=====Remarks=====
 +Matching is case-sensitive.
 +
 +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.
 +
 +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.
 +
  
 \\  \\ 
Line 41: Line 50:
  
  
-**Objective:** Merge the "Outflow" column of Table 2 into Table 1 based on matching values in the "Rivers" columns in both tables.\\+**Example:** Merge the "Outflow" column of Table 2 into Table 1 based on matching values in the "Rivers" columns in both tables.\\
  
 **Table 1:** The longest rivers in the world\\ **Table 1:** The longest rivers in the world\\
Line 62: Line 71:
  
 > Add columns from table: Table 2 > Add columns from table: Table 2
-> Where values in the Table 1 "River" column match values in the Table 2 "River" column+> 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) > Merge mode: Left join (add all matching rows from Table 2 to Table 1)
 > Return column: Outflow > Return column: Outflow
  
 \\  \\ 
-**Result:**\\+**Result table:**\\
  
 ^River  ^Length (km)  ^Continent  ^Outflow  ^   ^River  ^Length (km)  ^Continent  ^Outflow  ^  
Line 75: Line 84:
 | Mississippi  |  6275|North America  |Gulf of Mexico  | | 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//)
  
 \\  \\ 
Line 80: Line 96:
   * [[transformations:append|Append another table]]   * [[transformations:append|Append another table]]
   * [[transformations:lookup|Lookup]]   * [[transformations:lookup|Lookup]]
 +  * [[transformations:intervalmerge|Interval merge]]
  
transformations/merge.txt · Last modified: 2024/04/11 23:55 by craigt
Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki