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
Last revisionBoth sides next revision
transformations:merge [2021/04/05 00:33] – craigttransformations:merge [2023/10/11 20:49] – [Remarks] 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 three possible merge modes:\\
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===== +
-The resulting table after Merge can't have more than 134,217,728 rows.\\+
  
 \\ \\
Line 36: Line 32:
 |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 46:
  
  
-**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 67:
  
 > 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 80:
 | 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 92:
   * [[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