2020 Week 50 - Solution
Solution by Tom Prowse and you can download the full workflow here.
Secret Santa isn't quite the same this year, with drawing names out of a hat not being COVID friendly! So the challenge this week is all about using Tableau Prep to assign partners and prepare the emails to be sent out to each person.
Step 1 - Assign IDs
The first step this week is to assign an ID to each person so that we can use this when pairing people up. We want this to be done in an alphabetical order, and can use a Rank calculation to do this (Row_Number will also work here):
Row Number
Step 2 - Assign Matching ID
Next we want to create another ID which will be used to create pairs. We are told that the next name alphabetically will match and also that the last name will match with the first name. Therefore, we first need to identify what the last ID number is by using a Fixed LOD:
Max Row Number
We can now use the Max Row Number and Row Number fields to create a Matching ID field using this calculation:
Matching Row Number
IF [Max Row Number]=[Row Number] THEN 1
ELSE [Row Number]+1
END
Our data should now look like this:
Notice, how the Matching Row Number is the next number compared to the Row Number, apart from the final value where the sequence restarts.
Step 3 - Assign Pairs
Now we have both IDs, we can use a join to create the pairs. To do this we will use a self join:
With the join condition which looks like this:
Then after the join and removing some unwanted fields our data now looks like this:
Step 4 - Clean & Create Email
The final step of this week's challenge is to clean the email address field and then format the Email Subject and Body ready for us to send out.
First, we want to make sure that each email contains Name@secrectsanta.com, therefore we can use the following calculation to create this:
Email
[Secret Santa]+'@secretsanta.com'
This method works because all of the email domains are the same, however a different method would be required if you had multiple different emails.
We can now create the email subject with this calculation:
Email Subject
'Secret Santa 🤫🎅'
We can input emojis straight into calculations as a string field.
A shortcut to input these is:
Windows: ⊞ Win+. or ;
Mac: Command+Control+Spacebar
Finally we just need to format the Email Body using the following string calculation:
Email Body
[Secret Santa]+' the results are in, your secret santee is: '+[Secret Santa-1]+'. Good luck finding a great gift!'
We can then remove any additional fields and our data is now ready to output:
The full output can be downloaded here.
After you finish the challenge make sure to fill in the participation tracker, then share your solution on Twitter using #PreppinData and tagging @Datajedininja, @JennyMartinDS14, @JonathanAllenby & @TomProwse1
You can also post your solution on the Tableau Forum where we have a Preppin' Data community page. Post your solutions and ask questions if you need any help!
