Table of Contents
NATURAL MERGE
Category: Transform / Advanced
Description
This action merges two tables based on columns with the same name.
Use cases
Use this action to auto-merge two tables without having to manually select specific columns.
Action settings
| Setting | Description |
|---|---|
| Other table | Select the second table to merge into the current dataset. |
| Mode | Options: Lookup (for each row in this table, get any one matching row from the other table and ignore other rows), Inner join (mere only rows with values that exist in both tables), Left join (for each row get all matching rows from the other table), Full join (merge all matching and non-matching rows from the other table). |
Remarks
Merges are based on column names that match precisely between the two tables. Aside from the matching columns, all columns are appended to the original dataset based on matching values.
If there are no matching columns between the two tables, all columns from the other table will be appended to the original table, without values.
Examples
Example #1
Merge the following tables, based on their matching columns.
Before (source table)
Table 1: Last names with addresses
| LastName | Address |
|---|---|
| Smith | 13 Any Street |
| Sanchez | 9 South Street |
| Thomas | 29 East Avenue |
Table 2: Last names with phone numbers.
| LastName | PhoneNum |
|---|---|
| Sanchez | 222-222-2222 |
| Thomas | 333-333-3333 |
| Smith | 111-111-1111 |
After (result table)
| LastName | Address | PhoneNum |
|---|---|---|
| Smith | 13 Any Street | 111-111-1111 |
| Sanchez | 9 South Street | 222-222-2222 |
| Thomas | 29 East Avenue | 333-333-3333 |
Action parameters
Other table: Table 2
Mode: Lookup
Example #2
Merge the following tables. (Note that the tables do not contain any matching column names.)
Before (source table)
Table 1: Last names with addresses
| LastName | Address |
|---|---|
| Smith | 13 Any Street |
| Sanchez | 9 South Street |
| Thomas | 29 East Avenue |
Table 2: Last names with phone numbers.
| LName | PhoneNum |
|---|---|
| Sanchez | 222-222-2222 |
| Thomas | 333-333-3333 |
| Smith | 111-111-1111 |
After (result table)
| LastName | Address | LName | PhoneNum |
|---|---|---|---|
| Smith | 13 Any Street | ||
| Sanchez | 9 South Street | ||
| Thomas | 29 East Avenue |
Action parameters
Other table: Table 2
Mode: Lookup
Example #3
Merge the following tables where the Last Names appear in both tables.
Before (source table)
Table 1: Last names with addresses
| LastName | Address |
|---|---|
| Smith | 13 Any Street |
| Sanchez | 9 South Street |
| Thomas | 29 East Avenue |
Table 2: Last names with phone numbers.
| LName | PhoneNum |
|---|---|
| Sanchez | 222-222-2222 |
| Thomas | 333-333-3333 |
After (result table)
| LastName | Address | PhoneNum |
|---|---|---|
| Sanchez | 9 South Street | 222-222-2222 |
| Thomas | 29 East Avenue | 333-333-3333 |
Action parameters
Other table: Table 2
Mode: Inner join