2024: Week 46 - Solution
Solution by Tom Prowse and you can download the workflow here.
Step 1 - London Tube Stations
The first branch that we can focus on is the input with information about the London Tube stations. We want to rename a couple of the fields to remove the 'Right_' prefix so we are just left with the Station Latitude or Longitude. We can use the Rename Fields functionality to do this:
After renaming the fields we then want to identify any duplicate rows and then remove these from the workflow. Again, we can use Tableau Prep's inbuild functionality to identify the duplicate rows and then filter to keep only the unique values.
After removing the duplicates then we are ready to output our first table:
Step 2 - Attractions
Next we want to focus on our input that includes the information about the London Attractions and their footfall.
The first thing that we need to do is to ensure that the headers are identified within our table. There are a couple of options to do this including using the data interpreter or manually setting the 'Header and Data Start Row' within the input step:
Once we have identified the correct headers we can then exclude any rows where there is missing data. This is where there are values of 0 or '-' within a year field. So we can exclude the '-' values from 2021.
Then we can change the shape of our data so that we have all of the years in a single column. For this we want to use a columns to rows pivot with all of the years within the pivoted fields:
There is an option to use the wildcard pivot here to bring all of the years in at once.
Now we have all of the attraction footfall data in a single column we can multiply this value by 1000 and then find the 5 year avg across the years:
5 Year Avg Footfall
Then finally we can rank these attractions based on that average:
Attraction Rank
After this we can output our second table that looks like this:
Step 3 - Location Data
Our final branch is based on the Location Lat Long data. Within the input the lat long data is within the same field so therefore we want to split these out into separate fields.
To do this we can utilise Tableau Prep's functionality to automatically split the values. Alternatively you can use a custom split on the ',' (comma).
After the split you'll need to rename some fields, update the field types, and remove the original lat long field. The table should now look like this and it's ready for the output:
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!
Then head on over to Workout Wednesday to build a dashboard with the data.
