2024: Week 29 - Solution
Solution by Tom Prowse and you can download the workflow here.
Step 1 - End of Q1
We want to calculate the Q1 times so first need to make sure the Start Date field is recognised as a date field type. We can do this can changing the field type from a string to a date time.
Next we want to calculate the starting time by finding the earliest time across the whole data set. We can calculate this using a fixed LOD:
Starting Time
Then calculate the end time by adding 20 mins:
End of Q1
DATEADD('minute',20,[Starting Time])
Step 2 - Q1 Times
We are now in a position to work out the qualifying times within Q1. For this we want to filter all of the rows where the date_start field is less than the end of Q1 - [date_start]<[End of Q1]
Then aggregate the rows so we find the minimum lap_duration for each driver:
Then finally we want to rank the lap_duration to find the position:
Position
Then finally filter so we only return the bottom positions lower than 16 - [Position]>=16
The table for Q1 should look like this:
Step 3 - Q2 & Q3 Times
Now we want to focus on Q2 times, so we can create a new step off of the step where we calculated the End of Q1 but this time we want to filter for where the date_start is after the end of Q1 - [End of Q1]<[date_start]
We can then calculate the start of Q2 by finding the minimum value:
Q2 Start
Then we can calculate the end of Q2 by adding 17 minutes:
End of Q2
DATEADD('minute',17,[Q2 Start])
We can then follow a similar pattern as Q1, where we filter for dates before the End of Q2 - [date_start]<[End of Q2]
Then aggregate for the fastest lap per driver:
Then rank for positions:
And finally filter for positions over 11 - [Position]>=11
This is the table for Q2
We then want to follow the same process for Q3 but starting at the end of Q2 and not needing to calculate the end of Q3 time or filter the final positions.
The table for Q3 looks like this:
Then finally we can bring together the results from all of the qualifying rounds with a union so that the table looks like this:
Step 4 - Combine Drivers
The final step is to include the drivers. For this we can join the drivers table using an inner join on the driver_number:
Then if we want to sort the values so they are in the right order, then we can create another rank on the position and rename the fields.
The finally output should look like this:
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!
