2024: Week 38 - Solution
Solution by Tom Prowse and you can download the workflow here.
Step 1 - Initials Field
First we want to add the Engagements and the Initials data source into the workflow. From here we can join these together using an inner join on Consultant Forename and Initial ID.
We can then remove the Initial ID field and rename the Initial to Initial Forename.
Then we can create another join but this time it will be for the Consultant Surname and Initial ID.
After removing and renaming the fields, we can create an Initials field using the Forename and Surname:
Initials
[Initial Forename]+[Initial Surname]
Out table should now look like this:
Step 2 - Engagement Dates & Grades
Next we want to create dates for when the engagement started and ended. We can do this with the Makedate function:Engagement Start Date
MAKEDATE(2024,[Engagement Start Month],[Engagement Start Day])
MAKEDATE(2024,[Engagement Start Month],[Engagement Start Day])
Engagement End Date
MAKEDATE(2024,[Engagement End Month], [Engagement End Day])
We can then correct some of the grades as per the requirements by finding the minimum for each person:
Corrected Grade
We can then calculate the engagement order using the Start Date and Initials:
Engagement Order
Next we want to identify whether there are any engagements where the start date occurs before the previous engagement end date. For this we need to calculate the previous end date by using a lookup calculation:
Previous End Date
{partition [Initials] :
{orderby [Engagement Order] ASC:
lookup([Engagement End Date],-1)
}
}
Then we can create a boolean to identify when the dates overlap:
Start Date after Previous End Date
[Engagement Start Date] > [Previous End Date]
Then to remove these fields we can keep only the True values and remove any fields that we no longer need. The table should look like this:
Finally we can join the Grades table onto our workflow using an inner join on the Corrected Grade and Grade ID fields:
After removing any additional fields our final table 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!
