Preppin' Data 2020 Week 50

by Owen Barnes

The most recent Christmas edition of Preppin' Data is out, and the challenge involved some logic using the rank function. This blog post will walk through the process in solving this challenge!

The Challenge

The scenario here looks at matching people to their respective recipient of a secret santa gift. The idea is, you are matched with the person who is next after them in the alphabet. If we had John, Alex and Sarah- Alex would send a gift to John, John would send a gift to Sarah, and Sarah would send a gift to Alex ie. (1 -> 2, 2 -> 3, 3 -> 1)

The problem is, Prep isn't great when it comes to multi-row formula. This is something commonly explored in Prep, and it gives us a logical challenge to solve this problem. The logic is, rank the rows by the alphabetic order (starting from A); add 1 to that rank (unless you are last, then return the minimum rank number), then find a way to connect these two ranks together.

The final output

So let's start at the beginning.

Spot the errors

The first thing we need to do is sort out the email addresses. Notice the comma, and '!' instead of the dot? To solve this, I split the email up into two, grouped all of the values to one and retained the '' which removed the ',' and '!' errors. I then simply reconnected this together, using a calculated field of Email Split 1, '@' and Email Split 2. This results in a clean email address. You could also just use 'replace' in a calculation - eg. replace([Email], ',', '.') & replace([Email], '!','.').

It's time to rank our rows, ordered by whether they are first, second in the alphabet. Next, we simply add 1 to this to get our 'recipient'. When solving the logic for Tom (who needs to send to Ellie), I wanted to say 'if rank = maximum rank then return the minimum rank'. However we cannot mix aggregate and non-aggregates, so I just hard-coded this logic in.

We now have the following:

Here we can see, Rank1 is ranked alphabetically, and Rank+1 is who we need to match this person with (who the sender should send their gift to). To solve this, I created two separate flows, grouping by Rank (and Rank+1), and then taking the minimum of Email and Secret Santa. What this will do is pass through the strings (instead of requiring a method of aggregation such as 'count'.

The idea is, we now join on that rank value. So if the rank = 1, it will join to rank=1 from the other workflow, but these values are different (remember we added 1 to one of the ranks). This will connect people to who they need to send the gift to!

We now have 'Sender' and 'Receiver'. Now, we can define our email body to solve the challenge!

The final step is just some simple string concatenation.

I hope this logic can help in the future. I love challenges like these. If you want a similar challenge, check out this challenge!