A Use Case for Tableau Prep: Using a ‘Master Table’ to See All Records

Data

A Use Case for Tableau Prep: Using a ‘Master Table’ to See All Records

by Kathryn Bridges

Here’s the scenario: I work at Global Superstore. We sell products from lots of different sub-categories. I can always reference this from our Master Inventory File:

Sub-Category Table

I get weekly sales data sent to me, and my job is to make a weekly report for each region that gives them their sales. This month, I load in my data and it looks as follows. OK, so we didn’t make any sales in Appliances, Binders, Copiers, Phones or Storage:

Sub-Category Sales

My supervisor asks that we still show those so that each region knows there weren’t any sales.“No problem,” I think to myself. I pull in my master sub-category inventory list and left join my weekly sales data to that on the field of Sub-Category. Overall, looks good. I throw a ZN() in front of my Sum(Sales) to show zeroes where we didn’t have sales last week:

Sub-Category Sales Adjusted

And I go to add in my Region filter to show these weekly sales for each region:

Region Selection

If I select the region of interest, my sub-categories disappear again. Notice there’s also a Null region, as well. For those of you who are SQL pros, you may be a step ahead of me, but let’s walk through what’s happening. We have two tables, that look somewhat like the following:

Two Tables

When we left join on Sub-Category, our resulting table now looks as follows:

Left Join on Sub-Category

So, as you can see, anytime we filter on Region, we’re excluding any sub-categories that didn’t have sales that week. We could go back and select Null region as well, but that’s not intuitive for users at all. One potential solution is to create a second data source that gives us every combination of Sub-Category and Region as well as the ability to use that new datasource as our left join instead of our master list.

Cue Tableau Prep.

I want a record of every possible combination of Region and Sub-Category, so I first bring in my Master Sub-Category file. From my Master Sub-Category datasource, I group by Sub-Category. Next, I’ll also need a field to join on. Since I want every combination, I just create a calculated field called AllValues, which is just the string value All:

Tableau Prep: Joining on AllValues Field

Next, I bring in my Weekly Sales datasource and select to only see my Region field:

Tableau Prep: See Only Region Field

I group by Region and then create that same All Values field I did in my Master Sub-Category datasource:

Tableau Prep: Group by Region, Create Field

Now, I can join these two datasources on my field All Values field. Once I have my new table, I group by my fields Sub-Category and Region and then output this new Master Table back to an extract so I can use it in Tableau:

Tableau Prep: Master Table Extract

I run the flow, and I’m good to go!

Tableau Prep: Finished Running Flow

Alright, so now I can go back into that data connection window and set up the left join again so that the new file I just created in Tableau Prep (AllRegionSubCat) is being joined to my original weekly sales file – this time on both Region and Sub-Category:

Tableau Prep: Data Connection Window

Now, back in my view, I want to make sure I still have Sub-Category from my Master Table on Rows. Now, instead of filtering on the Region field from my Weekly Sales table, I have a Region field from my Master Table (AllRegionSubCat) that I can use instead. I bring the Region field from my AllRegionSubCat master file to Filters, and my view now looks like I want it to:

Final View in Tableau

No null Region Filter option. No disappearing sub-categories. If you aren’t able to do a join with your data, you could also set this up with a blend. As long as you have this Master Table as the primary datasource, it would work the same way.

The key here is that you want a table that gives you every unique combination of any fields in your view, including those that you’re filtering by. One of the easiest ways to create that table is using Tableau Prep!

More About the Author

Kathryn Bridges

Analytics Consultant
Matillion 101: Completing My First Job in Matillion If you’ve been following along in this Matillion series, you’ve already set up and logged into Matillion. From here, I’m going to talk ...
Time Travel with Snowflake A couple years ago, as I was trying to teach myself programming, I managed to wipe out my computer. It was a silly mistake—all I was ...

See more from this author →

Subscribe to our newsletter

  • I understand that InterWorks will use the data provided for the purpose of communication and the administration my request. InterWorks will never disclose or sell any personal data except where required to do so by law. Finally, I understand that future communications related topics and events may be sent from InterWorks, but I can opt-out at any time.
  • This field is for validation purposes and should be left unchanged.

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK

×

Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072