2021: Week 44 - Solution
Solution by Tom Prowse and you can download the workflow here.
Step 1 - Create KMs, Turbo Trainer & Outdoors Fields
The task this week is to input the data and then convert the value field to kilometres. From the requirements we know that Carl cycles at an average of 30km/h so we can use the following calculation to convert the values, that are in mins, to km's:
KMs
ZN(IF [Measure] = 'min' THEN ([Value]/60)*30
ELSE [Value]
END)
On a new step we can then convert the measure field by changing mins to 'Turbo Trainer' and kms to 'Outdoors'. We can do this by double clicking on the value in the profile pane and then renaming for each value.
Finally we want to use a rows to columns pivot so that we create a new field for Turbo Trainer and Outdoors:
After the pivot our data should now look like this:
Step 2 - Create New Rows
Now we want to ensure that we have a row for each date between the 1st Jan and 1st Nov 2021, therefore we need to create some rows using the add rows step. Instead, of adding this after the pivot tool, we need to back the first clean step then create the add rows on a new branch.
We set up the add new rows step like the following:
Then on the next clean step we can keep only the Date and Detail fields.
Now we can combine the two branches together by using an outer join which returns all of the fields from the branch where we added the new rows:
We need to do the add new rows on a separate branch as there seems to be some problems when we add this after the pivot step. Therefore by creating the new branch and joining back onto our data, we ensure that each individual day is returning the correct value.
Step 3 - Totals for Each Day
The final step is to aggregate our data so that we have a total for each day. We want to group by date, count type, then sum Turbo Trainer and Outdoors:
Then finally we turn any null values into 0s by using the ZN() function, rename Type to 'Activities per day', and remove any unneeded fields so that our output 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!
