In a previous blog, I showed how I completed some Alteryx Challenges that were simple as I had just started learning Alteryx. You can read that blog here In this blog I will be solving a more advanced Alteryx Challenge. Looking through them, one stuck out to me. Alteryx Challenge #126 replicates a game of Cluedo. Here is the challenge:

The Case: Mr. Boddy was recently on a trip to London when he unfortunately met with his untimely demise… But whodunnit?? In the spirit of the game Clue (a murder mystery game originally called Cluedo, created by Anthony E Pratt from Birmingham, England in 1949), follow the clues to figure out who committed this dastardly deed, where the crime was committed, and which weapon was used!

This challenge is separated into three sections:
Who is the culprit?
What was the weapon?
Where is the scene of the crime?

This blog will also be split into those three sections. Each section came with the needed datasets in order to figurer out the who, what and where of the murder. At the end we will enter our solution into the macro provided in the challenge to check our answer. Let’s move into the first section.

As you can see there are four sections to finding out who the murderer is. The first is formatting the phone numbers so they are the same for when we join. The phone directory table has the numbers as strings in the format like +1 555 867 5309 and the text message data table has the numbers as integers in the format like 442079460540. I used the data cleansing tool and select tool to match the formats. The next step is to use two joins to join the tables. We join phone number and to message and then phone number and from message. The third step is to calculate the time away from the murder which was done using the datetimediff formula. This is all the information needed to solve this part of the challenge. Using a sort and sample tool will give you who the murderer is. Now let’s move on to the part 2 of the challenge, ‘What was the weapon?’.

To find out what the murder weapon is, there are two sections. Formatting the provided tables into the form that we need them in and then we can find the weapon using the information provided in the challenge. Firstly, I used the text to columns tool using ‘|’ as the delimiter and then the dynamic rename tool to take the headers that were in the first row and make them the column names. With the first table, the credit card numbers only show the first and last four numbers where the full number is shown in the second table, so I used the formula tool to have the numbers match. Now the tables are in a better form so we can join them on the credit card numbers. Now for the next section, first you need to filter the table to only show transactions from who the murder culprit is. In this blog I will not actually give my final answer so you can go and try this challenge yourself. I did a summarize tool just grouping by item just so I can get a list of items. In the challenge, they provide you a list of potential weapons that we can now search for. Those items are a candlestick, knife, pipe, revolver, rope and a wrench. I simply used a filter tool to see which tool the murder culprit had purchased, and you will now have the murder weapon. Next, let’s solve the final question, where is the scene of the crime?

To find out where the murder happened, you need to use the make point tool to take the lat and long coordinates to create a point. The first data set contains the possible locations and another data set with the point of discovery. We will append the point of discovery to the dataset of possible locations. Now we have all the information to find the location of the murder. If you use the distance tool and the filter out all locations above one kilometer from the body. Once you do this, you should have one location in your table.

Now enter the murder culprit, weapon and location into the provided macro and it should tell you if you get the correct answer or not. It was a fun challenge that I had a lot of fun with. It was a great test of a lot of skills that I have learnt. As I am writing this, I am also studying for the advanced Alteryx certification, and I found this to be good practice on a lot of needed tools. Thank you for reading my blog.