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.
Does it require input data? ⛔ No — This phrase does not require input data.
Is it required? ✅ Yes — This phrase is required in the syntax.
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.
Does it require input data? ✅ Yes — Table References
Is it required? 🌟 No — This phrase is optional in the syntax.
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.
Does it require input data? ✅ Yes — Table Reference
Is it required? 🌟 No — This phrase is optional in the syntax.
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.
Does it require input data? ✅ Yes — table
Is it required? 🌟 No — This phrase is optional in the syntax.
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.
Does it require input data? ✅ Yes — List of Texts
Is it required? 🌟 No — This phrase is optional in the syntax.
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.
Does it require input data? ✅ Yes — List of Texts
Is it required? 🌟 No — This phrase is optional in the syntax.
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.
Does it require input data? ✅ Yes — List of Texts
Is it required? 🌟 No — This phrase is optional in the syntax.
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.
Does it require input data? ✅ Yes — List of Texts
Is it required? 🌟 No — This phrase is optional in the syntax.
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.
Does it require input data? ✅ Yes — List of texts in the format - the similar columns are "{column1}" and "{column2}", ...
Is it required? 🌟 No — This phrase is optional in the syntax.
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.
Does it require input data? ✅ Yes — List of Texts
Is it required? 🌟 No — This phrase is optional in the syntax.
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.
Does it require input data? ✅ Yes — Table Reference
Is it required? 🌟 No — This phrase is optional in the syntax.
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.
Does it require input data? ✅ Yes — Either "on" or "off". Default value is "on", if not specified.
Is it required? 🌟 No — This phrase is optional in the syntax.
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
