transformations:merge
This is an old revision of the document!
Merge another table
This transformation appends one or more columns from another table where certain columns in both tables match.
There are three possible merge modes:
- 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.
- Full Join Mode – include all combinations of keys from both tables. Equivalent to the full outer join in SQL.
EXAMPLE
Table 1: The longest rivers in the world
River | Length (km) | Continent |
---|---|---|
Nile | 6650 | Africa |
Amazon | 6400 | South America |
Yangtze | 6300 | Asia |
Mississippi | 6275 | North America |
Table 2: The outflow
River | Outflow |
---|---|
Nile | Mediterranean |
Amazon | Atlantic Ocean |
Yangtze | East China Sea |
Mississippi | Gulf of Mexico |
Objective: Merge the "Outflow" column of Table 2 into Table 1
Transformation:
- Add columns from table: Table 2
- Column: Outflow
- Merge mode: Add more rows to this table and all matching rows from Table 2 (i.e. do left join)
Result:
River | Length (km) | Continent | Outflow |
---|---|---|---|
Nile | 6650 | Africa | Mediterranean |
Amazon | 6400 | South America | Atlantic Ocean |
Yangtze | 6300 | Asia | East China Sea |
Mississippi | 6275 | North America | Gulf of Mexico |
See also
transformations/merge.1493693628.txt.gz · Last modified: 2017/05/01 22:53 by dmitry