Joins (CX)

About Joins

Joins allow you to combine rows from 2 or more data sources based on a related column of data that they share. By using a join, you can gather and analyze the combined data more efficiently and effectively, creating more insights.

The data modeler only supports left outer joins.

Understanding Left Outer Joins

To understand how a left outer join works, let’s look at an example.

Look at the image of the dataset below. The first data source on top is our “left” data source, and the second data source on the bottom is our “right” data source.

Let’s say these tables represent the data you can find in each survey:

Store Locations (Left Source)

Customer Feedback (Right Source)

You choose to join the data based on Location ID. This field is called a “join key.”

This is the data unique to the second dataset. This would be removed:

This is the final output, or all of the data that would be included in your results:

Notice how the Provo, Dublin, and Seattle results contain columns of data from both the Right and Left sources, because these rows shared common location IDs.

There was no data for Tokyo in the Right source, and so the Tokyo row has null values under the new Customer ID and Satisfaction columns.

Importance of Unique Join Keys

Because join keys help to identify rows that need to be combined form the left and right sources, we recommend making sure the join key you use acts like a unique identifier. Otherwise, if there are multiple records in the right source that match the join key of the left source, only one of them will be pulled randomly.

If the join key isn’t unique for each record on a right source, and you want to include all records from both the right and left source, a union should be used instead. Unions pull in each of the records separately instead of combining the rows of information.

Creating Joins

1. Create a data model.
2. Add at least 2 sources to your data model.
   
   Qtip: Make sure you include all the fields you need in your data sources, including the common field you’ll use to join your data (e.g., a unique ID).
3. Click the plus sign ( + ) next to the data source you want to act as your Left data source.
4. Select Join.
5. Name the output. This is helpful if you plan to add multiple joins to your dataset.
   
6. Under Input, select the Right data source.
7. Create a join condition. Match the field that each dataset has in common.
   Example: Here we are mapping our Unique ID field from each data source to each other.
   Qtip: Fields of any type can be used in join conditions. We highly recommend using a unique identifier that matches across both data sources.
   Qtip: Recodes on join keys can’t be used.
8. If you have at least 1 other, separate data source below the sources you joined, you can create another join using the existing join.
   
9. You can create separate joins in the same dataset. In this screenshot, you’d join the data from 2022 Survey and Actionability, but not the top 2 data sources.
   
10. Finish creating your data model with an output dataset.
    

Using Auxiliary Joins

Auxiliary joins let you join multiple sources with the same join condition. Thus, they are useful when you want to create multiple, overlapping joins using the same data sources.

Earlier, we talked about how left outer joins work with 2 sources: a right source and a left source. Auxiliary joins let you set multiple left sources for the same right source in a join.

Example of an Auxiliary Join

Let’s say you have a database of store locations, with names linked to IDs. You have 2 years of surveys where you collect feedback on your stores. In the example below, we’ll find the satisfaction rating and customer ID for 2020 and 2021, and link them to a location name.

Store Locations (Left Source)

Customer Feedback 2020 (Right Source)

Customer Feedback 2021 (Auxiliary Right Source)

You join the data based on Location ID.

This is the final output, or all of the data that would be included in your results:

Notice how 2020 and 2021 data have become separate columns in the same output dataset.

Because 2020 did not have data for Tokyo, but 2021 did, the 2020 columns are empty (N/A) for Tokyo. Likewise, 2021 had no Provo data.

Records with location ID “222” from either year were excluded from the final data source, since the store locations file had no corresponding location for that ID. See Understanding Left Outer Joins for an explanation of how data is excluded.

Creating an Auxiliary Join

1. Click the join.
   
2. Go to Join settings.
3. Click the plus sign ( + ).
4. Select the Right Input.
   
   Example: This is the second source you want to join to your left source. In our example above, this would be the 2021 customer feedback survey.
5. Set the left column’s join condition.
6. Set the right column’s join condition.

Repeat steps as needed to add more auxiliary joins.

Auxiliary Joins vs. Subsequent Joins

In addition to auxiliary joins, you can click the plus sign ( + ) next to your completed join and select Join. We’ll call this method a “subsequent join.”

Generally, auxiliary joins are useful when you want to create multiple overlapping joins using the same data sources. Subsequent joins are best if you want to create a join with 2 additional data sources that were not included in your first join.

However, there are times that auxiliary joins and subsequent joins can have the same output.

FAQs