Create a Merged Table

Merge two or more tables based on specified matching and merging criteria.

Overview

This procedure generates a merged table by combining data from multiple tables based on user-specified merge criteria. You can designate a master table and a working table and define how rows should be matched (exact or fuzzy match). Additionally, you can configure which columns to include or exclude, how to treat similarly named columns, and whether to sort the columns in the final output.

Syntax

Below is a line-by-line overview of the automation syntax. Expand each line to learn more.

create a merged table

What does it do?

Begins the procedure to merge tables using the specified parameters.

Where does it go?

This phrase should be written on a new line.

Is it required?

✅ Yes — This phrase is required.

Does it require data?

❌ No — This phrase does not require data.

the tables are

What does it do?

References the tables to be merged.

Where does it go?

Indented under create a merged table with.

Is it required?

❌ No — This phrase is optional.

Does it require data?

✅ Yes — Table References need to be provided.

the master table x

What does it do?

Specifies the table with which other tables will be merged. This table serves as the base for the merge operation.

Where does it go?

Indented under create a merged table with.

Is it required?

❌ No — This phrase is optional.

Does it require data?

✅ Yes — Replace x with a reference to a table.

Example

the master table is the above

the working table is y

What does it do?

Specifies the secondary table to be merged into the master table. This is considered when fuzzy or exact match columns are specified.

Where does it go?

Indented under create a merged table with.

Is it required?

❌ No — This phrase is optional.

Does it require data?

✅ Yes — Replace y with a reference to a table.

Example

the working table is the above

the master fuzzy match columns are "column1", "column2", "column3"...

What does it do?

Defines columns in the master table to use for fuzzy matching.

Where does it go?

Indented under create a merged table with.

Is it required?

❌ No — This phrase is optional.

Does it require data?

✅ Yes — Replace column1, column2, column3, etc. with the names of the columns.

the working fuzzy match columns are "column1", "column2", "column3"...

What does it do?

Defines columns in the working table to use for fuzzy matching.

Where does it go?

Indented under create a merged table with.

Is it required?

❌ No — This phrase is optional.

Does it require data?

✅ Yes — Replace column1, column2, column3, etc. with the names of the columns.

the master exact match columns are "column1", "column2", "column3"...

What does it do?

Defines columns in the master table to use for exact matching.

Where does it go?

Indented under create a merged table with.

Is it required?

❌ No — This phrase is optional.

Does it require data?

✅ Yes — Replace column1, column2, column3, etc. with the names of the columns.

the working exact match columns are "column1", "column2", "column3"...

What does it do?

Defines columns in the working table to use for exact matching.

Where does it go?

Indented under create a merged table with.

Is it required?

❌ No — This phrase is optional.

Does it require data?

✅ Yes — Replace column1, column2, column3, etc. with the names of the columns.

the similar columns are "column1", "column2", "column3"...

What does it do?

Specifies similarly named columns to consider for merging.

Where does it go?

Indented under create a merged table with.

Is it required?

❌ No — This phrase is optional.

Does it require data?

✅ Yes — Replace column1, column2, column3, etc. with the names of the columns.

the excluded columns are "column1", "column2", "column3"...

What does it do?

Specifies columns to exclude from the final merged table.

Where does it go?

Indented under create a merged table with.

Is it required?

❌ No — This phrase is optional.

Does it require data?

✅ Yes — Replace column1, column2, column3, etc. with the names of the columns.

the blacklist table is b

What does it do?

References a table containing rows that will be excluded from the merged table.

Where does it go?

Indented under create a merged table with.

Is it required?

❌ No — This phrase is optional.

Does it require data?

✅ Yes — Replace b with a reference to a table.

Example

the blacklist table is the above

the sort columns is {"on" | "off"}

What does it do?

Determines whether the columns in the final merged table are sorted alphabetically.

Where does it go?

Indented under create a merged table with.

Is it required?

❌ No — This phrase is optional.

Does it require data?

✅ Yes — Use either "on" or "off". Default value is "on", if not specified.

Example

the sort columns is "off"

Examples

1. Create a Basic Merged Table

create a merged table with
    the tables are the above tables

2. Create a Merged Table with Similar and Excluded Columns

create a merged table with
    the tables are the above tables
    the excluded columns are "FRIEND", "FOE"
    the similar columns are "ALIAS" and "NAME", "MONEY" and "WORTH"

3. Create a Merged Table without Sorting Columns

create a merged table with
    the tables are the above tables
    the sort columns is "off"

4. Create a Merged Table with Fuzzy and Exact Match Merge

open a master table at "s3://my-bucket/data/master_table.xlsx"
open a working table at "s3://my-bucket/data/working_table.xlsx"
open a blacklist table at "s3://my-bucket/data/blacklist_table.xlsx"
create a merged table with
    the master table
    the working table
    the master fuzzy match columns are "name", "age"
    the working fuzzy match columns are "nom de guerre", "life span"
    the master exact match columns are "address"
    the working exact match columns are "location"
    the blacklist table