transformations:merge
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revisionNext revisionBoth sides next revision | ||
transformations:merge [2018/04/30 09:44] – dmitry | transformations:merge [2021/04/07 14:18] – [Full Join Mode] dmitry | ||
---|---|---|---|
Line 1: | Line 1: | ||
- | ===== Merge another table ===== | + | {{ transformations: |
+ | ====== | ||
+ | Category: Transform / Basic\\ | ||
- | This transformation | + | \\ |
+ | =====Description===== | ||
+ | This action | ||
- | There are three possible merge modes: | + | There are three 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.\\ | ||
- | * 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. | + | =====Limitations===== |
- | * Full Join Mode -- include all combinations of keys from both tables. Equivalent to the full outer join in SQL. | + | The resulting |
- | EXAMPLE | + | \\ |
+ | =====Use cases===== | ||
+ | * (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. | ||
- | **Table 1:** The longest rivers | + | \\ |
+ | =====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. | ||
+ | |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).| | ||
+ | |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 | ||
- | ^ River ^ Length (km) ^ Continent | + | \\ |
- | | Nile | 6650 | Africa | + | =====Examples===== |
- | | Amazon | + | |
- | | Yangtze | + | |
- | | Mississippi | + | |
- | **Table 2:** The outflow | ||
- | ^ River ^ | + | **Objective: |
- | | Nile | Mediterranean | + | |
- | | Amazon | + | |
- | | Yangtze | + | |
- | | Mississippi | + | |
+ | **Table 1:** The longest rivers in the world\\ | ||
- | **Objective:** Merge the " | + | ^River |
+ | | Nile | 6650|Africa | ||
+ | | Amazon | ||
+ | | Yangtze | ||
+ | | Mississippi | ||
+ | \\ | ||
+ | **Table 2:** The outflow\\ | ||
- | **Transformation:** | + | ^River |
+ | | Nile |Mediterranean | ||
+ | | Amazon | ||
+ | | Yangtze | ||
+ | | Mississippi | ||
+ | \\ | ||
+ | **Action parameters:** | ||
- | * Add columns from table: Table 2 | + | > Add columns from table: Table 2 |
- | * Column: Outflow | + | > Where values in the Table 1 " |
- | | + | > Merge mode: Left join (add all matching rows from Table 2 to Table 1) |
+ | > Return column: Outflow | ||
- | **Result:** | + | \\ |
+ | **Result:**\\ | ||
- | ^ River ^ Length (km) ^ Continent | + | ^River |
- | | Nile | 6650 | Africa | + | | Nile | 6650|Africa |
- | | Amazon | + | | Amazon |
- | | Yangtze | + | | Yangtze |
- | | Mississippi | + | | Mississippi |
- | === Limitations === | ||
- | The resulting table after Merge can't have more than 134,217,728 rows. | ||
- | === See also === | + | \\ |
+ | =====See also===== | ||
* [[transformations: | * [[transformations: | ||
+ | * [[transformations: | ||
transformations/merge.txt · Last modified: 2024/04/11 23:55 by craigt