2020: Week 27 - Solution

You can find our full solution below and download it here. 

For this week's challenge we were planning our staycation and looking to find the best surf location for each of our requirements and abilities. 

Step 1 - Join Location & Information

Our first step this week is to combine the Information and the Location sheets. Our information sheet is ready to go, so we don't need to make any changes, however the Location sheet contains ' - South Devon' on the end of each of the sites therefore we want to remove this. 

To do that, we use the custom split to return the 1st field before the '-' separator. We are now left with just our site names, and these are ready to join onto the Information table. 

This join is a simple Inner join, where the Site = Surf Site: 

We now have one table for both the Information & Location which should look something like this: 

Step 2 - Join Preppers Table

Now out Information & Location tables are combined, it's time to focus on the Preppers table. This includes the requirements and abilities for each of us when looking for a surf holiday so it's an important table for the overall outcome, but it's not quite in the correct shape to join onto our original workflow.

First, we want to split the Season field so that we have all of the different options that are split by a ',' and then we want to repeat this for Board Type as well. As a result we should have a table with each of the different options from those two categories in a separate column:

Now they are all in different columns, we want to use a pivot field to bring them all back into one. We use the following pivot steps:

1. Pivot Boards

2. Pivot Season

As a result of these two pivots, we now have a column for both the Season and Board Type and a row for each of the different possibilities for each member of the team.

Notice how there are some blank values in some of the rows, this is fine as they will be removed at a later stage.

The final step before we join the Preppers table to our original workflow is to create a join field. For this we are going to use a calculated field which just equals 1. This will be the same on both tables and will allow use to join every possible outcome from both of the tables.

Notice how there are lots of extra rows as a result of this join. That's because we are joining 1 from both sides, therefore each row is joining with each other row, eg. 32 * 16 = 512 rows.

Step 3 - Is the Location Suitable?

Now we have all our tables combined into a single table, we can start to identify the surf sites that would be suitable for our holiday. First we need to tidy the table a little bit as we have a lot of extra rows that aren't required. 

1. Replace Empty with Nulls

First we want to remove empty values from the Board Type and Season fields. We can do this by right-clicking on the empty fields then choosing 'Replace with Null'.

2. Identify Suitable Sites

Next we are going to use a calculation to identify whether or not the site is suitable for our needs. As we have pivoted our data so that we have a new row for each of the Board Types/Seasons, we can use some Contains logic to identify if the surf site is right for us.

Suitable?

IF 
CONTAINS([Surf Season],[Season Split])
AND
CONTAINS([Skill Level],[Skill])
AND
CONTAINS([Boards],[Board Type Split])
THEN 1
ELSE 0
END

This calculation is identifying whether or not our split/pivoted fields contain any of the attributes from the Preppers table. If it contains all of the requirements then return 1, if it doesn't then return 0. 

3. Filter Unsuitable Sites

We should now have a 0 or 1 for each of the rows, where 1's are suitable and 0's aren't. Therefore, we can use the filter to remove the 0's so we are left with only suitable surf sites for each member. 

4. Remove Duplicates

As a result of our 1-1 join earlier in the flow, we may have some duplicated rows at this stage. Therefore, we can use the Aggregation tool to remove any of these with the following setup: 

Here we are grouping by all of our required fields and then bringing back to average rating, which will remove any duplicated rows that we may have. 

Step 4 - Calculate Ranks

1. Rank by Rating

The final step for this week's challenge is to calculate the ranking of each surf site. First we are going to rank the sites based on their rating: 

For this rank we want to group by Name, as we want a rank for each different person, and then order the Rating from largest to smallest. 

2. Filter Top Ranks

Now we have ranked our Surf Sites, we want to Keep Only the sites that are ranked 1, for each Prepper. Therefore we can use the filter to remove any other ratings. 

3. Reliability Weighting

This allows us to identify the highest rankings for each of our Preppers, but there are some sites with the same ranking. As a result, within the Challenge Post we are told to look at Reliability if there is a tie. 

For the reliability we want to use the following calculation to classify their weight within the rank: 

Reliability Rating

CASE [Reliability]
WHEN 'Rarely Breaks' THEN 1
WHEN 'Inconsistent' THEN 2
WHEN 'Fairly Inconsistent' THEN 3
WHEN 'Fairly Consistent' THEN 4
WHEN 'Very Consistent' THEN 5
END

This allows us to convert each of the strings into a number, which we can then use to rank our surf sites again. 

4. Reliability Ranking

Now we have a weighting for each Reliability string, we can use another rank to rate these: 

Then again we want to filter to Keep Only the fields ranked number 1. 

Finally, we want to remove any fields that aren't required so that our table is ready for the output. 

This is the final output that is required: 

Make sure to fill in the participation tracker, share using #PreppinData on Twitter and post you solutions onto our Tableau Forums community page so that we can compare our workflows!