2023: Week 41 - Solution
Solution by Tom Prowse and you can download the workflow here.
Step 1 - Input Tables
First we want to input both of our data tables into the workflow. The next instruction is to join both of these tables together using an inner join on Student ID, however, this isn't actually required as this will only bring in the name of each of the students and this isn't something that we require to complete the task.
We can join these tables but for efficiency we are only going to work from the Nationality table.
Step 2 - Clean Nationality
Next we want to clean the spelling errors in the Nationality field. To do this we can use Tableau Prep's in-built functionality which will group values based on similar spellings. We can adjust the sensitivity until we have the correct grouping that we require:
Step 3 - Count Students
We can now create a count of students based on the classroom and nationality. For this we want to use an aggregation step to group by classroom and nationality and aggregate by counting the student ID.
Step 4 - Rank
The final step is to rank the classrooms based on the number of students. We want to rank the nationalities for each classroom so within our rank calculation we can group by classroom, then rank the student ID in a descending order:
Finally we only want to keep the highest rank for each classroom so we can select the 1 value from the rank field within the profile pane, then select keep only.
Our final table should look like this:
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!
