2022: Week 34 - Solution

Video Solution - 

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

This week we have a deeper dive into using Parameters in Tableau Prep and how we can use them to make different selections when filtering our data. We are looking at the different rides from some Spin Classes where we want to know the top rides for different coaches and music types.

Step 1 - Clean Metrics

The first step is to clear up the table so that we have the correct metrics. First we need to rename Value to Mins then we can remove the Units and Type fields. This will give us the duration of each ride. 

Next, we need to split apart the F5 field to get the information about the Coach, Calories, and Music Type. We can do this by splitting all values on '-':

After the split we can rename the fields so our table should look like this: 

The last task we need to do in this step is to make sure the Music Type is in Title Case (we covered this a couple of weeks ago in a challenge!). The calculation that we can use to do this is: 

Music Type 

IF 
CONTAINS([Music Type],' ') 
THEN 
UPPER(LEFT([Music Type],1)) 
+
MID([Music Type],2,FIND([Music Type],' ')-2) 
+
' ' 
+
UPPER(MID([Music Type],FIND([Music Type],' ')+1,1)) 
+
MID([Music Type],FIND([Music Type],' ')+2)
ELSE
UPPER(LEFT([Music Type],1))+MID([Music Type],2)
END

This helps to highlight each of the spaces within the string, then makes the next character a capital.

Our table should now look like this:

Step 2 - Create Parameters

The next step is to create the parameters that will be used to control the filters to create the desired output. We need to create the following parameters:

Coach - Select a coach

This is a String parameter that allows you to select name from a list

Music Type - Select a Music Type
This is also a String with a list of all of the different types of Music

Top N - How many records to select
This is a number parameter where the user can type in a custom number

Step 3 - Create Filters

Next we want to use the parameters to filter our table and show the correct information. We can create the following filters:

Coach
[Coach]=[Parameters.Coach]

Music Type 
[Music Type] = [Parameters.Music Type]

Both of these filters will return True depending on what is selected in each of the parameters.

Finally we need to create a filter based on a Top N. This is based on a ranking of the highest calories burned, so we first need to rank the calories field: 

After the ranking, we can then create the final filter to create our final table.

Rank 
[Rank]<=[Parameters.Top N]

This time we want the rank to be less than or equal to the Top N parameter selection.

Step 4 - Create Output

All we need to do now is create the output step. Within the step we need to include the parameters in the name of the output, we can do this by using the parameter selection so that the output name looks like this: 

Output Name

PD 2022 Wk 34 Output Top<Top N> for rides with <Coach> powered by <Music Type>

An example of the output would look like this: 

You can download an example 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!