2019: Week 36 Solution

You can view our full solution workflow below and download it here!

Our full solution workflow.

1.   Convert the height for the Spurs.

Firstly, we’re going to create the height conversion step. There’s a number of different ways to convert the feet & inches into meters here. An easy method is as follows:

1. Remove all punctuation from [HT] using the relevant Clean option.
2. Use an automatic split to split [HT] into [Feet] (split 1) & [Inches] (split 2).
3. Calculate the [Height (in)] (height in inches) using: [Feet] * 12 + [Inches].
4. Convert this to [Height (m)] (height in meters) using: [Height (in)] * 2.54 / 100.
   We divide by 100 as without it we actually have the height in centimetres.
5. Round this to 2dp using the ROUND() function.

After this, make sure to remove the original [HT] fields as well as all the unnecessary fields created along the way.

2.   Convert the weight for the Spurs.

We’re now going to create the weight conversion step. There’s less work to be done in order to convert the weight.

1. Remove all letters and spaces using the relevant Clean options.
2. Convert this to [Weight (KGs)] using INT( [WT] ) * 0.453592.
3. Round this to 2dp using ROUND().

After this, make sure to remove the original [WT] field.

3.   Clean up the names and get the jersey numbers for the Spurs.

Before saving and re-using our conversion steps, we’re first going to complete the flow for the San Antonio Spurs by cleaning up their name field and getting their jersey numbers. Our method is as follows:

1. Duplicate the [NAME] field. Rename the [NAME-1] duplicate field as [Jersey Number] as this is what it will become after some cleaning.
2. Using the Clean settings, clean up the [NAME] field by removing all numbers.
3. Using the Clean settings, clean up the [Jersey Number] field by removing all letters, punctuation, and spaces.

Now we have a full prepared data set and workflow for the Spurs. Time to re-use this work for the Brooklyn Nets!

4.   Save the height conversion & weight conversion steps.

To save our height and conversion steps and make them re-usable for future workflows, simply select both steps (either via clicking and dragging over both steps or via holding shift whilst selecting both steps) and select Save Steps as Flow > Save to File. Give your flow a name and save it somewhere ready to be re-used in the future.

Saving the conversion steps.

If you don’t have this option then you need to upgrade to 2019.3.2 which you can do here (or downgrade if using the current 2019.4.1 beta as of 27/10/2019).

If upgrading isn’t an option for you then you can use the Copy Steps option instead in this instance. The main limitation of copied steps compared to saved steps is that you can’t copy and paste steps between different Tableau Prep windows.

5.   Import and re-use your saved steps.

After loading in the data for the Brooklyn Nets, right click anywhere on the canvas and select ‘Insert Flow’. Click the green ‘Upload Flows’ button, navigate to your saved flow and click open. After a few seconds it should appear on your canvas!

Link it up to your Nets data by dragging your Nets data and dropping it onto of your imported steps – it should have ‘Add’ highlighted whilst doing this opposed to ‘Join’ or ‘Union’.

Importing our saved steps.

And voila – you should find that your Nets data now gets a nicely converted height and weight field with zero errors. After this, simply repeat the name & jersey clean-up (either via: copying the step; saving it as it’s own workflow; or manually re-configuring everything if you hate yourself) and you’re done!