How do you create a cross join in Tableau Prep?

When embarking on a data communication project, you might not always have all the data you need to create a prototype in a timely manner. I often generate realistic, placeholder data sources so I can design a dashboard and get feedback, even if the actual data isn’t ready for display yet.

Take this Stadium Seating Dashboard for instance:

57-1.png

It is a prototype designed after a dashboard which a friend at a Major League Baseball team showed me. I spent hours generating the data for this dashboard. On top of the custom shapes for the sections (we won’t get into that here), I needed to generate seating data for each section for each possible game. In order to do that, I needed a cross (cartesian) join.

So what is a “cross join”?

It is a join that matches every row of data from one table to every row of data from another table. These rows do not need to have a shared dimension to match.

For the above “Stadium Seating” example, let’s imagine there were only 10 games and 24 sections in the stadium. We would eventually need a table with 240 rows (a unique row per game and section) to be able to generate ticket sales data.

Imagine we have a “Game” table that looks like this:

57-2.png

And a “Section” table that looks like this:

57-3.png

To create a cross join, we load both tables into Tableau Prep and join where Year (from the Game table) does not equal (!=) Section ID (from the Section table).

57-4.png

The result is that these tables of 10 and 24 rows were then turned into 240 rows:

57-5.png

 

From there, I spent additional time generating columns of data for attendance and sales figures, but that is another post for another time.

Two quick notes:

●        You can accomplish a cross join in Tableau Desktop too.

●        When you are selecting which fields to perform a cross join on, make sure they don’t share any values. Because the Year field was 2018 and the Section ID field was 1-24, there were no instances where the values were equal. If there had been (i.e. there was a section 2018), that row would not be included in the cross join.

 

Want to learn more about what Tableau Prep can do for you? Check out our Tableau Prep Foundations Workshop.