User Tools

Site Tools


transformations:merge

MERGE ANOTHER TABLE

Category: Transform / Basic


Description

This action appends one or more columns from another table where the values in certain columns in both tables match. The action is similar to the SQL join, with four modes: Lookup, Left Join, Full Join, and Inner Join.


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.


Action settings

SettingDescription
Add columns from tableSelect a table that will return columns to the current dataset where values match.
Where all these columns matchSelect the columns within the current dataset (left drop-down) and the second dataset (right drop-down) where
values will match. To define multi-columnar matches, use the Add more matching columns to select additional matching columns in each dataset.
Merge modeOptions:
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)
Inner join (add rows that match in both tables).
Return columnsFrom 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.


Remarks

Matching is case-sensitive.

Values must be identical (equal) to be matched. If another matching mode is needed (e.g. "Starts with") use the Match action first to do the matching.

For an inner join use the Keep/remove matching action before merging to keep only the overlapping subset of values in the matching columns.


Examples

Example #1

Merge the "Outflow" column of Table 2 into Table 1 based on matching values in the "Rivers" columns. Keep only rows that match in both tables.

Before (source table)

Table 1: The rivers of the world

River Continent
Nile Africa
Amazon S. Amer.
Yangtze Asia
Mississippi North America

Table 2: The outflow

River Outflow
Nile Mediterranean
Amazon Atlantic Ocean

After (result table)

River Continent Outflow
Nile Africa Mediterranean
Amazon S. Amer. Atlantic Ocean

Action parameters

Add columns from table: Table 2
Where values in Table 1's "River" column match Table 2's "River" column
Merge mode: Inner join
Return column: Outflow


Example #2

Merge the "Outflow" column of Table 2 into Table 1 based on matching values in the "Rivers" columns.

Before (source table)

Table 1: The rivers of the world

River Continent
Nile Africa
Amazon S. Amer.
Yangtze Asia

Table 2: The outflow

River Outflow
Nile Mediterranean
Amazon Atlantic Ocean
Yangtze East China Sea

After (result table)

River Continent Outflow
Nile Africa Mediterranean
Amazon S. Amer. Atlantic Ocean
Yangtze Asia East China Sea

Action parameters

Add columns from table: Table 2
Where values in Table 1's "River" column that match Table 2's "River" column
Merge mode: Left join
Return column: Outflow


Community examples


See also

transformations/merge.txt · Last modified: 2025/01/13 16:11 by craigt

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki