transformations:merge
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
transformations:merge [2018/04/30 09:44] – dmitry | transformations:merge [2025/01/13 16:11] (current) – craigt | ||
---|---|---|---|
Line 1: | Line 1: | ||
- | ===== Merge another table ===== | + | {{ transformations: |
+ | ====== | ||
+ | Category: Transform / Basic\\ | ||
- | This transformation | + | \\ |
+ | =====Description===== | ||
+ | This action | ||
- | There are three possible merge modes: | + | \\ |
+ | =====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. | ||
+ | * (Inner Join Mode) Connect only rows from both tables where the values in the selected fields match. | ||
- | * Lookup Mode -- get **first** matching combination | + | \\ |
- | * Left Join Mode -- get **all** | + | =====Action settings===== |
- | * Full Join Mode -- include all combinations of keys from both tables. | + | ^Setting^Description^ |
+ | |Add columns | ||
+ | |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: | ||
+ | |Return columns|From the list, select the columns from the second dataset that will be appended | ||
+ | The reference to the second table will appear as a dotted line connecting this action to the second dataset | ||
- | EXAMPLE | + | \\ |
+ | =====Remarks===== | ||
+ | Matching is case-sensitive. | ||
- | **Table 1:** The longest rivers in the world | + | Values must be identical (equal) to be matched. If another matching mode is needed (e.g. " |
- | ^ River ^ Length (km) ^ Continent | + | For an inner join use the [[transformations: |
- | | Nile | 6650 | Africa | + | |
- | | Amazon | + | |
- | | Yangtze | + | |
- | | Mississippi | + | |
- | **Table 2:** The outflow | ||
- | ^ River ^ Outflow | + | \\ |
- | | Nile | Mediterranean | + | =====Examples===== |
- | | Amazon | + | |
- | | Yangtze | + | |
- | | Mississippi | + | |
+ | ====Example #1==== | ||
+ | >Merge the " | ||
- | **Objective:** Merge the " | + | ===Before (source table)=== |
+ | Table 1: The rivers of the world | ||
+ | ^River | ||
+ | | Nile |Africa | ||
+ | | Amazon | ||
+ | | Yangtze | ||
+ | | Mississippi | ||
- | **Transformation:** | + | Table 2: The outflow |
+ | ^River | ||
+ | | Nile |Mediterranean | ||
+ | | Amazon | ||
- | * Add columns from table: Table 2 | + | ===After (result |
- | | + | ^River |
- | * Merge mode: Add more rows to this table and all matching rows from Table 2 (i.e. do left join) | + | | Nile |Africa |
+ | | Amazon | ||
- | **Result:** | + | ===Action parameters=== |
+ | > Add columns from table: Table 2 | ||
+ | > Where values in Table 1's " | ||
+ | > Merge mode: Inner join | ||
+ | > Return column: Outflow | ||
- | ^ River ^ Length (km) ^ Continent | ||
- | | Nile | 6650 | Africa | ||
- | | Amazon | ||
- | | Yangtze | ||
- | | Mississippi | ||
- | === Limitations | + | \\ |
- | The resulting table after Merge can't have more than 134,217,728 rows. | + | ====Example #2==== |
+ | >Merge the " | ||
- | === See also === | + | ===Before (source table)=== |
+ | Table 1: The rivers of the world | ||
+ | ^River | ||
+ | | Nile |Africa | ||
+ | | Amazon | ||
+ | | Yangtze | ||
+ | |||
+ | Table 2: The outflow | ||
+ | ^River | ||
+ | | Nile |Mediterranean | ||
+ | | Amazon | ||
+ | | Yangtze | ||
+ | |||
+ | ===After (result table)=== | ||
+ | ^River | ||
+ | | Nile |Africa | ||
+ | | Amazon | ||
+ | | Yangtze | ||
+ | |||
+ | ===Action parameters=== | ||
+ | > Add columns from table: Table 2 | ||
+ | > Where values in Table 1's " | ||
+ | > Merge mode: Left join | ||
+ | > Return column: Outflow | ||
+ | |||
+ | |||
+ | \\ | ||
+ | =====Community examples===== | ||
+ | * [[https:// | ||
+ | * [[https:// | ||
+ | * [[https:// | ||
+ | * [[https:// | ||
+ | * [[https:// | ||
+ | |||
+ | \\ | ||
+ | =====See also===== | ||
* [[transformations: | * [[transformations: | ||
+ | * [[transformations: | ||
+ | * [[transformations: | ||
transformations/merge.1525095847.txt.gz · Last modified: 2018/04/30 09:44 by dmitry