Tableau Class Notes: A Use Case for the Self Join

Data

Tableau Class Notes: A Use Case for the Self Join

It’s no secret that Tableau Prep is a game changer for those looking to clean, explore and connect their data sources together. There are myriad join options, from your traditional left, right and inner joins to more advanced options such as Left Unmatched Only, which will include only rows that exist on the left data source. But one join type eluded me—the self join. So when my student, Greg, asked me for a good example of a self join, I knew why I would do it but not how.

Enter Harry Potter.

Here’s what I am trying to accomplish: I have a list of all the Harry Potter films, the year of their release and what their sequel is:

self joins in Tableau Prep

Using the Self Join in Tableau Prep

I want a new data source that will show me a few new columns per movie: what is the sequel’s release date, and what is the sequel’s sequel? For example, for Harry Potter and the Sorcerer’s Stone, I should still see the release date of 2001, the sequel should still be Harry Potter and the Chamber of Secrets, but I also want a column for the release date of 2002 for Chamber of Secrets, and for Prisoner of Azkaban as the sequel to Chamber of Secrets.

In order to get that data source, I can do a self join in Tableau Prep (note that this could also be achieved in Tableau Desktop). While this isn’t a pre-defined join option, I can join my Sheet 1 to my Sheet 1. What’s interesting is my join clause, and this took a bit of experimentation.

Finding the Right Join Clause

The challenge I encountered was, how do I join my two instances of the same sheet together in order to get the information I wanted? I needed to make sure that I was pulling in each sequel’s release date as well as its own sequel, information contained in the three columns of my original data source:

self joins in Tableau Prep

If I join my two worksheets with Sequel=Movie, I can then obtain information for that sequel’s release date, as well as the sequel’s sequel:

self joins in Tableau Prep

Of course, I will have one result excluded. As the first film in the series, Harry Potter and the Sorcerer’s Stone is not a sequel. I can discard this, add a clean step and voila! I have all the data I need:

self joins in Tableau Prep

Self joins are a great alternative to what might have been a very tedious copy-paste situation. Thank you, Greg, for the challenge!

More About the Author

Liz Jensen

Analytics Consultant
Alter Your Analysis: Women’s World Cup Match Wins In this second installment of Alter Your Analysis, I am tackling Alteryx’s Weekly Challenge 169: “Using the values in the attached file ...
Alter Your Analysis: Highest Average Retail Rating While attending my first Alteryx training with InterWorks’ own Steph Kennedy, she said something that still sticks in my brain each ...

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