transformations:merge
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
transformations:merge [2017/01/01 15:32] – dmitry | transformations:merge [2024/04/11 23:55] (current) – add inner join craigt | ||
---|---|---|---|
Line 1: | Line 1: | ||
- | ===== Merge another table ===== | + | {{ transformations: |
+ | ====== | ||
+ | Category: Transform / Basic\\ | ||
- | This transformation | + | \\ |
+ | =====Description===== | ||
+ | This action | ||
- | There are two 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.\\ | ||
- | | + | \\ |
- | * Left Join Mode -- get **all** matching combinations | + | =====Use cases===== |
+ | | ||
+ | | ||
+ | * (Full Join Mode) Combine two entire datasets to be cleaned and consolidated downstream. | ||
+ | | ||
- | To make full outer join, first, append rows that exist in the other table but are missing | + | \\ |
+ | =====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: | ||
+ | |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. | ||
- | EXAMPLE | + | \\ |
+ | =====Remarks===== | ||
+ | Matching is case-sensitive. | ||
- | **Table 1:** The longest rivers | + | Values must be identical (equal) |
- | ^ 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 | + | |
- | **Objective:** Merge the " | + | **Example:** Merge the " |
- | **Transformation:** | + | **Table 1:** The longest rivers in the world\\ |
- | * Add columns from table: Table 2 | + | ^River |
- | | + | | Nile | 6650|Africa |
- | * Merge mode: Add more rows to this table and all matching rows from Table 2 (i.e. do left join) | + | | Amazon |
+ | | Yangtze | ||
+ | | Mississippi | ||
+ | \\ | ||
+ | **Table 2:** The outflow\\ | ||
- | **Result:** | + | ^River |
+ | | Nile |Mediterranean | ||
+ | | Amazon | ||
+ | | Yangtze | ||
+ | | Mississippi | ||
+ | \\ | ||
+ | **Action parameters:** | ||
- | ^ | + | > Add columns from table: Table 2 |
- | | Nile | 6650 | Africa | + | > Where values in Table 1 "River" column match values in Table 2 " |
- | | Amazon | + | > Merge mode: Left join (add all matching rows from Table 2 to Table 1) |
- | | Yangtze | + | > Return column: Outflow |
- | | Mississippi | + | |
- | === See also === | + | \\ |
+ | **Result table: | ||
+ | |||
+ | ^River | ||
+ | | Nile | 6650|Africa | ||
+ | | Amazon | ||
+ | | Yangtze | ||
+ | | Mississippi | ||
+ | |||
+ | \\ | ||
+ | ====Community examples==== | ||
+ | * [[https:// | ||
+ | * [[https:// | ||
+ | * [[https:// | ||
+ | * [[https:// | ||
+ | * [[https:// | ||
+ | |||
+ | \\ | ||
+ | =====See also===== | ||
* [[transformations: | * [[transformations: | ||
+ | * [[transformations: | ||
+ | * [[transformations: | ||
transformations/merge.txt · Last modified: 2024/04/11 23:55 by craigt