2023: Week 28 - Solution

Solution by Tom Prowse and you can download the workflow here.

We have another challenge focussed on the Prep School this week but this time we are looking at the athletic abilities of our students. The challenge is from Habeeb Gayle so lets look how we can solve it.

Step 1 - Combine Tables

First we want to combine all of our tables together so we have a single table to work from. First we want to join the Students and the Track Times using an inner join on the ID field:

Then we can join the Benchmarks table using an inner join where Gender = Gender, Age = Age, Track_Event = Event: 

When we've joined these tables we can remove the duplicated fields and the table should look like this:

Step 2 - Filter Results

We can now start to filter our results to meet the requirements. First we want to remove anyone who hasn't met the benchmark by using a filter calculation [time]<=[Benchmark].

Next, we want to filter to remove any errors. We can highlight any rows which are seen to be errors by creating an Error Flag: 

Error Flag

IF [Event]='200m' AND [time]<25

THEN 'Error'

END

Then we can filter on this field and exclude any 'Error' values from the data set and then remove the Error Flag field.

Finally, we want to rank our results so that the fastest times are at the top. We can do this by ranking our table within each Event:

Rank 

Now we are ready to output our table which should look like this: 

You can download 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 @habeeb_gayle, @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!