2024: Week 27 - Solution
Solution by Tom Prowse and you can download the workflow here.
Step 1 - Join Stage & Stage Type
First we want to input the Stages and Stage Type tables in our workflow and we can join these together using an inner join where Stage Type = Stage Type ID
We can then remove the fields F4, Date, Day, Stage Type, and Stage Type ID so our table look like this: 
Step 2 - Time Trial?
Next we want to identify whether the stage was a time trial or not. For this we can use an IF statement alongside the Contains function to identify the ITT or TTT notation within the Stage field:
Time Trial?
IF Contains([Stage],'(ITT)') THEN 'Individual'
ELSEIF Contains([Stage],'(TTT)') THEN 'Team'
END
We will then have a Individual, or Team if the stage was a time trial or Null if it was not a time trial.
Step 3 - Origin, Destination & Stage Number
The information for the origin, destination and stage number are contained within the Stage field. Therefore we can use a custom split to extract these using the | as a separator:
This gives us two new fields that we can use for the stage and origin - destination. The Split-1 is the stage, so we need to remove all letters and punctuation (using the in-built Tableau Prep features) then replace the blank value with a 0. Once this only contains numbers we can then change the data type to a whole number.
The origin-destination field is a little easier as this just needs to rename the split-2 field to be 'Origin - Destination' and then we can remove the original Stage field.
At this stage the data looks like this:
Step 4 - Join Wins
We can now join the Wins table but first we need to make some changes. In order to join correctly we need to have a field for the Year and the Stage Number Won just for the Tour de France.
First we want to split the Race field using a | as a separator:
This provides us with 2 fields with the Race (split-1) and the Stage (split-2).
Within the split-2 we can replace the Prologue with a 0, and then remove the punctation and letters, and any nulls so we are left with the stage number.
Next we want to change the date to be just the Year = YEAR([Date]) then filter the Race field to just contain 'Tour de France'.
Once we have removed any additional fields we should have a field for Race, Stage Number Won, and Year.
We are now ready to join this to our original workflow using a left join on Year and Stage Number. Make sure the join contains all values from our original workflow, this might be a right or left join depending on how you have created the join.
After the join we want to remove the Race, Year, and rename Stage Type-1 to Stage Type. The table should look like this:
Step 5 - Grand Tour Starts
Next we want to join the Grand Tour Starts table and again we need to make some changes before we join the table to our original workflow.
On the Grand Tour Starts table we first want to filter the Grand Tour field to just contain 'Tour de France' values. Then we can remove all fields so we only have Season, GC (rename to General Classification Finishing Position), and Points (rename to Points Finishing Position) remaining.
Then we can join to our workflow using an inner join where Season = Year:
Finally we can identify whether or not he won the stage by creating a Stage Won flag:
Stage Won?
IF ISNULL([Stage Number Won]) THEN NULL
ELSE 'Yes'
END
Then our final output should look like this:
You can view the outputs 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!
