Table Join Tricks with Tableau

Have you been asked to get data sits in different tables? You need to find a common field to create a relationship between these tables. But what if they do not have common fields?

SQL is a way to join tables, but you have to design a logic and write several lines, which is powerful but time-consuming. Analysts are always seeking easier and faster ways to implement data manipulation. Today, I'm going to talk about a better way to join tables.

If you forget the basic knowledge of four types of table join, please find a very detailed explanation here.

In the Data Source tab, you'll see three tables (Orders, Returns and Users) in the Superstore Sales data set. You want to create a view to show the orders that were returned. Now let's do the table join.

I suggest exploring your data before manipulation and analysis. A fast way is to import data into Tableau and drag them to the canvas and view the field name and types.

Drag Order table first and then drag Return table next to it, you will see a red "!" because these two tables have no common field name. No worries, you know they both have order id which can be used to join. Simply click the arrow to select the field name of each table.

Analysts are not always so lucky. why? Sometimes there's no column to join two tables. But we can find a way to create such a field to join them. Join calculations are powerful in Tableau. Let's Explore it.

Now you have two datasets - 'Club membership' and 'Sales'. They look like this:

We want to answer this question: Which club member has the greatest sales? Before we do with the data, let's break down this question to several small points.

🤪Yuchen's tip: Always break down a complex question to simple points and finish them step by step

• We want to know the total sales of each customer;

• It requires us to join two datasets;

• There's no common field to join -> choose Join Calculation

Two ways to create join calculation:

1. Split the customer names from 'Membership Code' column in 'Club Membership' and combine the 'First Name' and 'Last Name' from 'Sales' (two calculations)

2. Take the customer id from 'Membership Code' column in 'Club Membership' then join 'Sales' with ID (one calculation)

🤪Yuchen's tip: Think more solutions before an action - the first idea shows up in your brain is not always the best way to get the thing done.

We choose 'Create Join Calculation' this time and type in this formula 'INT(LEFT([Membership Code],5))'

Click 'OK' button and choose 'ID' in the 'Sales' table to join two tables

Here we go:

We can create views to do some analysis with the combined dataset and find a business solution based on the data.

Here we have a bar chart to show the sales of each club member. We can see Tom Ashbrook contributed the most to the club's sales. You can also help the business manager to explore the profits of the members and build a loyalty program sending club activities to attract valuable customers to come back often.

🤪Yuchen's tip: It's meaningless to show charts to CEOs - tell them a story!

Happy analyzing with Yuchen!