User Tools

Site Tools


transformations:naturalmerge

NATURAL MERGE

Category: Transform / Advanced


Description

This action merges two tables based on columns with the same name.


Use cases

Use this action 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. All columns, aside from the matching 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: Perform a natural merge on the following tables, based on their matching columns.

Table 1: Names with addresses

FirstName LastName Address
Robert Smith 123 Any Street
Julie Rodriguez 999 South Street
Tammy Thomas 454 North Avenue


Table 2: Names with contact information

FirstName LastName PhoneNum Email
Julie Rodriguez 222-222-2222 julie@myemail.com
Tammy Thomas 333-333-3333 tammy@myemail.com
Robert Smith 111-111-1111 robert@myemail.com


Action parameters:

Other table is "Table 2"
Mode is "Lookup"


Result:

FirstName LastName Address PhoneNum Email
Robert Smith 123 Any Street 111-111-1111 robert@myemail.com
Julie Rodriguez 999 South Street 222-222-2222 julie@myemail.com
Tammy Thomas 454 North Avenue 333-333-3333 tammy@myemail.com



EXAMPLE #2: Perform a natural merge on the following tables. Note that the tables do not contain any matching column names.


Table 1: Names with addresses

FirstName LastName Address
Robert Smith 123 Any Street
Julie Rodriguez 999 South Street
Tammy Thomas 454 North Avenue


Table 2: Names with contact information

FName LName PhoneNum Email
Julie Rodriguez 222-222-2222 julie@myemail.com
Tammy Thomas 333-333-3333 tammy@myemail.com
Robert Smith 111-111-1111 robert@myemail.com


Action parameters:

Other table is "Table 2"
Mode is "Lookup"


Result:

FirstName LastName Address FName LName PhoneNum Email
Robert Smith 123 Any Street
Julie Rodriguez 999 South Street
Tammy Thomas 454 North Avenue


See also

transformations/naturalmerge.txt · Last modified: 2024/06/29 16:21 by craigt

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki