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.

Prerequisite Data

These required data elements must be present in the automation before using the procedure.

Data Name
Data Type

the tables

Table (at least two tables must be provided or referenced)

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 in the syntax.

Does it require input data?

⛔ No — This phrase does not require input data.

the tables are

What does it do?

References the tables to be merged.

Where does it go?

This phrase should be indented beneath create a merged table with.

Is it required?

🌟 No — This phrase is optional in the syntax.

Does it require input data?

✅ Yes — Table References

the master table

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?

This phrase should be indented beneath create a merged table with.

Is it required?

🌟 No — This phrase is optional in the syntax.

Does it require input data?

✅ Yes — Table Reference

the working table

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?

This phrase should be indented beneath create a merged table with.

Is it required?

🌟 No — This phrase is optional in the syntax.

Does it require input data?

✅ Yes — table

the master fuzzy match columns are

What does it do?

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

Where does it go?

This phrase should be indented beneath create a merged table with.

Is it required?

🌟 No — This phrase is optional in the syntax.

Does it require input data?

✅ Yes — List of Texts

the working fuzzy match columns are

What does it do?

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

Where does it go?

This phrase should be indented beneath create a merged table with.

Is it required?

🌟 No — This phrase is optional in the syntax.

Does it require input data?

✅ Yes — List of Texts

the master exact match columns are

What does it do?

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

Where does it go?

This phrase should be indented beneath create a merged table with.

Is it required?

🌟 No — This phrase is optional in the syntax.

Does it require input data?

✅ Yes — List of Texts

the working exact match columns are

What does it do?

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

Where does it go?

This phrase should be indented beneath create a merged table with.

Is it required?

🌟 No — This phrase is optional in the syntax.

Does it require input data?

✅ Yes — List of Texts

the similar columns are

What does it do?

Specifies similarly named columns to consider for merging.

Where does it go?

This phrase should be indented beneath create a merged table with.

Is it required?

🌟 No — This phrase is optional in the syntax.

Does it require input data?

✅ Yes — List of texts in the format - the similar columns are "{column1}" and "{column2}", ...

the excluded columns are

What does it do?

Specifies columns to exclude from the final merged table.

Where does it go?

This phrase should be indented beneath create a merged table with.

Is it required?

🌟 No — This phrase is optional in the syntax.

Does it require input data?

✅ Yes — List of Texts

the blacklist table

What does it do?

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

Where does it go?

This phrase should be indented beneath create a merged table with.

Is it required?

🌟 No — This phrase is optional in the syntax.

Does it require input data?

✅ Yes — Table Reference

the sort columns is "on"

What does it do?

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

Where does it go?

This phrase should be indented beneath create a merged table with.

Is it required?

🌟 No — This phrase is optional in the syntax.

Does it require input data?

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

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

Last updated

Was this helpful?