There are many instances in which you will need to join tables together and most of the time you this will be when one or more fields equal each other. However, there are some use cases for conducting non-equi joins. This is where we join data on a relationship that is inequal to the other. These cases are:
- Bigger than or equal to: >=
- Less than or equal to: <=
- Bigger than: >
- Less than: <
- Not equal to: !=
To use non-equi joins in Tableau Prep, have your two tables in the canvas and drag one to the other and hover over 'join'. Next, add a join using the plus icon and this will show a drop down where you can select which fields you would like to join by. Now all you need to do is click on the equals sign (the default clause type) and this will show a drop down of all the non-equi clauses for you to choose from.
A great example of using a non-equi join clause is in Preppin' Data Challenge 2023 Week 16; below I will take you through the join part of this challenge (ignoring other requirements).
The aim of the challenge is to find which full moon corresponds to marking the Easter Sunday. We start off with two input tables: the day, date and time of full moons from 1900; and the date of Easter Sundays from 1700.
To start with you will need to change the data type of the 'Date' field in the full moon date table. This is to allow us to join on Date as the data type must be the same for any join to occur.
Next, we start thinking about our join. How do we find which moon corresponds to the Easter Sunday? We want to find full moons that have occurred before the Easter Sunday; therefore, we must have a join clause of 'Full Moon Date <= Easter Sunday Date'. This will show all full moons that have occurred before or on that Easter Sunday.
But wait! This is joining all of the full moon dates before that Easter Sunday date from previous years!
So, before we join the two tables together we must use some logic. Lets make sure that the full moon has at least occurred in the same year as the Easter Sunday. To do this, duplicate the 'Date' field and extract the year (using convert dates>year number). Do this in both tables.
Now we can go ahead with our join. In addition to having the non-equi join clause of 'Full Moon Date <= Easter Sunday Date', add another join based on the our newly created year fields; 'Full Moon Year'='Easter Sunday Year'. Using these two join clauses will return all the full moons before that Easter Sunday within that year.
Don't be alarmed by the mismatched values as, due to our join clauses, there will be lots of dates of full moons that won't match! For the Easter Sundays, there is no data for the full moons before 1900, so any Easter from 1700-1899 will not be included. Similarly, we have only asked for the full moons before the Easter Sunday within that year; thus any full moon after Easter and before the following year will also not be included!