2020: Week 51 - Solution
Solution by Tom Prowse and you can download the full workflow here.
This week the NBA is back so we took a look at how many hours each of the 30 teams will spend on planes this season flying around North America.
Step 1 - Clean Matrix
The first task this week is to convert the matrix into something that is a little more useful for us. To do this we want to take the Travel Time Matrix table and use a columns to rows pivot to bring all of the teams into a single column. The setup looks like this, where all of the teams are included in the pivot values:
After the pivot our data should now look like this:
Step 2 - Convert Travel Time
Now we have converted the matrix into a more suitable structure, we can now start to clean the Travel Time field. We want to convert this into minutes, therefore we will need to remove any letters, and transform the hours into minutes.
1. Time Type
Using this calculation we can identify whether the time field is made up of hours and mins or just mins:
IF CONTAINS([Travel Time],'h') AND CONTAINS([Travel Time],'m') THEN "HM"
ELSEIF CONTAINS([Travel Time],'h') THEN 'H'
ELSE 'M'
END
We now have a column with the time identifier which looks like this:
2. Split Travel Time
It's now time to split the Travel Time field using a custom split on the space (' ') so that we have two columns. After the split we now have a column for hours and minutes, but some of the minutes are in the hours field.
3. Clean Hours & Minutes
Now we have the two separate columns, we need to clean these so that we can convert to minutes only. First we want to remove letters from both columns using Tableau Prep's clean functionality. We can then convert the Split 1 field into just minutes using the following calculation:
Hours to Mins
IF [Time Type] = 'H' OR [Time Type] = 'HM' THEN INT([Split 1])*60
ELSEIF [Time Type] = 'M' THEN INT([Split 1])
END
4. Total Mins
Now we have converted both columns to minutes we can add these together to get the travel time for each:
Total Mins
zn([Minutes])+[Hours to Mins]
Using the ZN function will convert any nulls values into 0s.
Once we have cleaned up the remaining fields our table should look something like this:
Step 3 - Clean League Structure
We can now turn our attention to the League Structure table, where we want to extract the city from each of the team names. This is structured so the last word of each team needs to be removed, meaning that it's not quite as simple as using the split functionality as some teams have two words, and others have three.
First we want to identify if the team has 1 or 2 spaces within their name by using the following calculation:
Double Space
FINDNTH([Team],' ',2)
Now we have identified if there are two or three words we can using the following calculation to extract the location part from the team name:
City
IF [Double Space] = 0 THEN SPLIT([Team],' ',1)
ELSEIF [Double Space] > 0 THEN SPLIT([Team],' ',1) +' '+ SPLIT([Team],' ',2)
END
As a result of this calculation our table now looks like this:
Step 4 - Join Travel Distance & League Structure
We are now ready to join our two workflows together. We need to do this twice, once from Home teams and then another for To teams.
The first join for Home teams looks like this:
After this join we can remove the Home and City fields, and then rename the following:
- Team to Home Team
- Conference to Home Conference
- Division to Home Division
We can now join on the To field with the following join:
As a result of both of these joins we now have all of the different match combinations that we need to calculate the travel time. Our table looks like this:
Step 5 - Calculate Total Travel Time
The final step of this week's challenge is to calculate the total travel time for each team. We need to adjust the travel time slightly as stated in the requirements:
Travel Mins
IF [Home Conference] = [Away Conference] THEN [Total Mins]*1.5
ELSE [Total Mins]
END
After this calculation we can use an aggregate to find the total travel time for each team, using the following setup:
We are now ready to output the data:
The full output can be downloaded 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, @JonathanAllenby & @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!
