transformations:merge
This is an old revision of the document!
Blend in/ Merge table
This transformation appends one or more columns from another table where certain columns in both tables match.
There are two 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.
To make full outer join, first, append rows that exist in the other table but are missing in the current table using Keep Different and Append Table transformations and then Merge (in Left Join mode).
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.1429457954.txt.gz · Last modified: 2015/04/19 11:39 by elenap