2022: Week 25 - Solution
Solution by Tom Prowse and you can download the workflow here.
This week is all about using Prep to assign hotel rooms so we can find a suitable room for each of our guests.
Step 1 - Additional Requests
First we want to focus on the Guests table of our input, where we need to replace any of the 'N/A' additional requests with a Null value. We can do this by right-clicking on the 'N/A' row in the Profile Pane, and then selecting 'Replace with Null'.
After this we want to split apart the Additional Requests field so that we have a field for each extra request. We can split these apart by using a ',' as a separator:
As a result we now have 3 extra fields, each with an individual request in them:
Now we can reshape the data so that we have all of the requests in a single field. To do this we can use a Columns to Rows pivot, with each of the Split fields being used.
After the pivot we can replace any of the blank ('') fields with a Null, then filter the table based on the Null values in both the Additional Requests and Additional Requests Split field. The filter calculation should look like this:
ISNULL([Additional Requests])
OR
NOT ISNULL([Additional Requests Split])
Finally we want to calculate the number of requests from each by parsing the number from the Pivot1 Names field. This will give us the number of additional requests based on how many extra fields were created when splitting.
To clean the Pivot Names field, we need to remove and letters & punctuation from the field, then trim any additional spaces. After parsing the number, we can rename the field to Number of Requests.
As there are multiple rows per party, we can use an aggregation tool to bring back to original number of rows and the total the number of requests. In the aggregation, we want to group by Party, Adults, Children, Double/Twin, Requires Accessible Room?, Additional Request and then find the MAX Number of requests:
The last step is to make sure the Number of Requests has been calculated properly when there are 0 additional requests. We can use this calculation to make sure 0 is correctly showing:
Number of Requests
IF ISNULL([Additional Requests]) THEN 0
ELSE [Number of Requests]
END
At this stage our Guests table is ready to combine with out Hotel Rooms. The Guest table should look like this:
Step 2 - Match Guests & Rooms
We can now start to work on the 2nd input, Hotel Rooms. Before combining this with our Guests table, we first need to ensure that the Children field is populated correctly, and we replace any Null values with a 0. We can use the ZN function for this:
Children
ZN([Children])
We can now join our tables together using an inner join on Adults and Children fields. We need to ensure that the Hotel Rooms have a large number of Adults and Children compared to the Guests field:
After the join we can rename some fields by using the Rename Fields functionality. We want to remove the -1 from both fields, and replace this with 'in Party' instead:
After the join our table should look like this:
Step 3 - Room Preferences
We can now start to filter out the rooms based on the requirements and requests from each party.
First we can remove any rooms that don't match the Double/Twin preference using the filter:
CONTAINS([Features],[Double/Twin])
We can then use another filter to ensure that the accessibility needs are met:
[Requires Accessible Room?]='N'
OR
([Requires Accessible Room?]='Y' AND CONTAINS([Features],'Accessible'))
Then finally we need to ensure that additional requests are met with the following calculations:
Bath
IF CONTAINS([Additional Requests],'Bath') AND CONTAINS([Features],'Bath') THEN 1
ELSE 0
END
High Floor
IF CONTAINS([Additional Requests],'High Floor') AND CONTAINS([Features],'High Floor') THEN 1
ELSE 0
END
Not Near to Lift
IF NOT CONTAINS([Features],'Near to lift') AND CONTAINS([Additional Requests],'NOT Near to lift') THEN 1
ELSE 0
END
After calculating the 1 or 0 for each of these, we can then determine whether the additional requests are satisfied by using the following calculation:
Additional Requests Satisfied
[Bath]+[High Floor]+[NOT Near to lift]
At this stage our table looks like this:
Step 4 - Most Suitable Room
We can now calculate how 'satisfied' a party will be based on their requests and room assignment. First we can use the following calculation to calculate their Request Satisfaction %:
Request Satisfaction %
IF ISNULL([Additional Requests]) THEN 100
ELSE
round(
100*[Additional Requests Satisfied]
/
[Number of Requests]
,0)
END
Then we can calculate the maximum for each of the scores, by each party:
Then finally we can filter the table and match the most suitable room for each party using the following calculation:
[Most Suitable Room]=[Request Satisfaction %]
The final area is to ensure that larger parties are prioritised so we want to remove any parties that could fit into smaller rooms.
First we need to find the maximum capacity for adults in each of the rooms:
Most Adults for Room
Then using this field we can filter the data using this calculation:
[Adults]<3 or
[Most Adults for room]=[Adults in Party]
This is our final step so our data is now ready to output and should look 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!
