2022: Week 43 - Solution

 

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

The challenge last week is an extension from last week. Once we solved last week, we then realised that it wasn't quite right and there were additional requirements. One of the benefits of using Tableau Prep is that it's easy to iterate on solutions and make changes to previous workflows.

Step 1 - Previous Flow

First, let's remind ourselves of the workflow from last week: 

We need to replace the data source so that we have the updated data. We can do this by editing the existing connection or adding a brand new one then changing our input step.

Step 2 - First Training Session

Now we have our updated data, we want to create a new branch after the ID step. 

On this branch we want to find the date for the first session. To do this we can keep only when the ID = 1 and then find the Min Date across the whole data source using a Fixed LOD: 

Min Date

Using the min date, we can now add in the dates between the min date and first session by using the New Rows step. We want to fill the dates where Min Date <= Date by an increment of 1 day - we call this field Session Date. 

Step 3 - Pre First Session Flag

Finally on this branch we want to create a flag for sessions that are before the first session. 

First, we want to filter the data where [Date] != [Session Date] and then we can create a Score field with the value 0. 

The next step is to create a 'Flag' by using a calculation with the string - 'Pre First Session'. 

At this stage the table from the new branch should look like this:

The final task that we need to do is to combine it with the previous rows that we created last week. We can combine them using a union and we want to place this between the Actual Flag and Remove Weekends step. If you have set your flow up differently you need to make sure you are removing weekends after you've combined the tables.

Once we have combined the data we can now output our table that looks like this: 

You can download the full 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!