2024: Week 31 - Solution
Solution by Tom Prowse and you can download the workflow here.
Step 1 - Fill Down Fields
Within the input step we can rename the fields in the Results table as per the requirements. This includes making the fields sentence case and removing the additional 800 field.
We can then fill in the null values with the actual position for each athlete. For this we want to use the Fill Down calculation where we order by the source row number and compute using the Position:
We can then remove the original position field and rename our new field to Position. We can then repeat the same with the Athlete, Nationality, and Total Points fields:
Athlete
Nationality
Total Points
At this stage the table will look like this:
Step 2 - Total Points
We now want to remove any letters from the Total Points field. We can do this by using the in-built functionality within Tableau Prep to remove letters and then trim spaces.
Now we have the total points as a number we can transform the shape of the table by pivoting the table so we have a row for each different event. For this we will use a Columns to Rows pivot with each of the events in the pivoted fields:
Then we want to pivot the table back the other way using a Rows to Columns pivot so we have a column for each Breakdown and the Max Value:
At this stage the table look like this:
Step 3 - Combine Events Lookup
We're now ready to combine with the Event Lookup table by joining this to our workflow using an inner join on Event:
We then want to split off the position from the Rolling Total and Event Time/Distance field.
For this we can use a the split functionality by splitting the value using the ')' separator and returning all fields. You might be able to use the automatic split but sometimes this will return a null value.
After the split you will also need to remove any additional ')' by using the remove punctuation clean functionality. You can then rename the split values, remove the original fields and replace the 'NM' value in the Event Time/Distance with a Null.
Finally we can sort the table using a rank calculation:
We only use this field to sort the table so we can hide the sort before outputting the data:
You can view the output 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 & @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!
