Join requirements and types

To create a Join between two tables, you need at least one column in each table that contains the same values. These common columns create the link between the two tables. When you create a Join, Tableau automatically uses the columns that have the same name as links. If there are no columns with the same name, you have to select the common columns manually in the Join menu that opens when you click on the Join icon.

The following screenshot illustrates how you can choose the columns when you click on the icon:

If there are no common columns, you can also create a Join Calculation (you'll learn how to create a calculation in Chapter 10, An Introduction to Calculations).

Sometimes, you have two columns with some values in common, but not all the values. It's up to you to decide how to deal with that by choosing the correct Join type: Inner, Left, Right, or Full Outer. To select a Join type, click on one of the four icons in the Join menu.

To illustrate the different Join types, I created an Excel file, Join example.

If you want to reproduce the example, you can download the Join example file from my website, book.ladataviz.com in the Chapter 4: Connect To Data and Simple Transformation section or use this direct link: https://ladataviz.com/wp-content/uploads/2018/09/Join-example.xlsx.

The Excel file contains two sheets. The first one is named Left table and contains the following data:

The second sheet, named Right table, contains the following data:

In Tableau, connect to the Join example file, and create a Join between the two tables. The link is the Common column column. Here's the screenshot of the Join in Tableau:

Now let's see the difference between the four different Join types in Tableau:

Inner Join (default): Tableau keeps only the lines with common values between the two tables. In the example, those are the b and c values. The result is displayed in the following screenshot:

Left Join : Tableau keeps all the lines from the left table, and adds the information from the right table if the values match. If there is no match, Tableau puts null. In the example, Tableau keeps the a, b, and c values but puts null in the Info column for c. The result is displayed in the following screenshot:

Right Join: Tableau keeps all the lines from the right table, and adds the information from the left table if the values match. If there is no match, Tableau puts null. In the example, Tableau keeps the b, c, and d values but puts null in the Value column for d. The result is displayed in the following screenshot:

Full Outer : Tableau keeps all the lines from the two tables. If the values don't match, Tableau puts null. The result is displayed in the following screenshot:

As you can see, Joins are very powerful. Be careful; there are some risks.

Table of Contents for Join requirements and types

Create new playlist

Sign In

Sign Up

Table of Contents for
Join requirements and types