One of the most important aspects of your work in Tableau is the data source you are
using to perform analysis.
Quite often the data will be stored in multiple locations, and hence you will have to deal
with a number of data sources which live in different environments.
Nevertheless, you are going to want to use all of the data available to you and run analysis
on everything together.
The way we merge multiple data sources is by using joins.
For those of you who have already followed our program and especially our SQL videos,
you’ll be familiar with what follows in this video, so feel free to skip or continue
watching as a refresher.
For the rest, please follow along.
When we want to perform ‘cross data table joins’ we want to combine two or more data
tables to create a unique database.
How do we join separate data tables?
Well, there are a few ways to do that.
We can create an inner, outer, left, or a right join.
Let’s open an Excel file to demonstrate a bit better what each type of join represents.
Here we have two very simple tables.
The first one shows us the age of three basketball players, and the second one shows us the salary
of basketball players.
Please, note that the two tables are different due to their last rows.
We have LeBron James in the first one, and Kyrie Irving in the second.
Ok.
Let’s say we would like to run some analysis and use the data available in both tables.
Therefore, as described earlier, we have to use joins.
But how do we do that?
We can easily see that the two tables have one column in common - the “Basketball Player”
column.
This column will serve as a key when we put together the information from both tables.
A left join would mean that the left column of the first table will lead the way.
We will use it to create a table containing age and salary information about the three
players we see here.
Whenever we find one of these players to the right, we’ll add their salary in the new
table, as you can see here.
If their name is not present to the right, which is the case with LeBron James’ salary,
we will have a null value in the table.
If a player’s name is not present in the left column of the first table, we will not
include any information about them, as this is a left join and any rows which are not
present in the key field of the left table are omitted in the new table.
A right join functions in the same way.
However, this time the left column of the second table leads the way.
Kyrie Irving replaces LeBron James who is not present in the left table.
Hence the only missing value would be Kyrie Irving’s age (given that the only information
we have about him is in the right table).
The case when we are interested in the intersection of the two tables only is called an inner
join.
This is when we create a table that contains rows where we have an exact match between
the key fields we are joining the two tables with (in our case “Basketball player”).
This time the newly created table contains two rows only.
Both tables contain information about these players, hence this is an inner join.
An outer join would be the opposite case.
We add all rows of the two tables regardless of whether there is match in the key field
we are linking with.
When there isn’t, we would have null values, which is the case with both LeBron James and
Kyrie Irving here.
These are the main principles you need to understand when deciding whether to create
a left, right, inner, or outer join in Tableau.
Depending on your needs and on the specific case you are working on, you will be able
to apply one of these structures and join your data.