While prepping your data in Tableau Prep, you’ll likely find yourself needing to combine different data sets to create a more comprehensive and consistent data set. This frequently happens through joins. Joins allow you to combine rows from two or more tables, based on a related column between them to enrich your original data set with additional information.
But what can you do if there are no related columns between the data sets? We can perform a cross join! A cross join, also referred to as a Cartesian join, is a joining method that produces the Cartesian product of rows originating from the tables involved. In other words, it pairs each row from the initial table with every row from the secondary table.
For example, let’s say we have these two tables:
Table 1 is a data set for a call center holding information on every call center agent (135 agents). The table contains the agent name, agent ID, leader name, leader ID, location, and location ID.
Table 2 is a list of every first day of each month in 2021.
Let’s say we want to record the agent’s performance for all 12 months. To do so, we would need to combine table 1 and table 2 to create 12 different rows for each of the 135 agents.
Since the two tables have no columns in common, we’ll need to create a dummy variable in tableau prep to perform a cross join.
Step 1) Connect to our data and import Table 1 and Table 2 into Tableau Prep.
Step 2) Add a clean step for table 1 and click to create a calculated field. Let’s name the calculated field “Dummy Variable” and input only a “1” for the calculation.
Step 3) Repeat step 2 for Table 2.
Step 4) Now that we have a common field for our two tables, we can create our join step. The Applied Join Clause will be Dummy Variable = Dummy Variable. Our join result will have 1,620 rows (135 agent x 12 months).
Step 5) Create a final clean step and remove the Dummy Variable fields.
Our resulting table now shows every agent for every month.
Hopefully this simplifies combining tables with no common fields in Tableau Prep!