Combine tables
Last updated
Last updated
The Combine Tables step joins multiple tables into one by matching rows between those tables. It is similar to a VLOOKUP in Excel or Google Sheets. In SQL, it's like a join clause.
The principle is simple: if we have two tables of data that are related to each other, we can use the Combine Tables step to join them into one table by matching rows between those tables. This step can handle combining two tables at a time. Once we set it up, we can use it repeatedly. Even if the row amount changes, our step will continue working.
The Combine Tables step requires two data inputs to combine. In our example below, we have two tables feeding into it. The first one is a table of "First Name", "Last Name", "Company", "Email" and "Country". The second one is a table of "Countries" and "Capital".
After using the Combine Tables step, our output data (shown below) has combined the "Countries" and "Capital" columns from the second table to the first table using "Countries" as the matching identifier.
After connecting two datasets into this step, Pick the datasets in the "First Dataset" and "Second Dataset" fields, then type your required join type "INNER", "OUTER", "LEFT" or "RIGHT". Then, in the identifier textboxes, you need to type the column name or index that will be used for the matching between the two datasets. "Identifier (First)" from the "First dataset" and "Identifier (Second)" from the "Second dataset". Once you're done, click the Preview results button from the bottom bar to display the newly-combined tables. If the datasets you want to combine don't have a shared column of identical values, then you can use the step Insert row numbers to make one in each table. You'd do this by connecting your import datasets to an Insert row numbers step for generating a column with incrementally-increasing numbers.
The default setting for the Combine Tables step is the most common way to combine tables. It keeps the entire primary table and finds matches in the other tables, fitting them in to their matched rows as we go. If a row doesn't have a match in the primary table, it won't show up in the results.
To combine three or more tables together, chain together multiple Combine Tables steps to merge your data.
