2024: Week 18 - Solution
Solution by Tom Prowse and you can download the workflow here.
Stage One
Step 1: Animal & Plant Details
First we need to input the Animal Details and Plant Details tables and combine them by using a Union step. We can then ensure that all the fields are correctly mapped by using the selections in the Union tool.
The miss-matched fields that need to be combined are Animal & Plant and Status & Stattus. These can be selected and then added together by using the + symbol.
We can then rename any fields and remove fields that we no longer required. The table should look like this:
Then using the Table Names field we can extract whether the row of data is related to an Animal or a Plant. In this case we can utilise the automatic split functionality where Tableau Prep identifies the part of the string that we want to extract and creates a new field for Animal or Plant.
We can then focus on the Class field to extract the English name instead of the Latin name. For this we can use the Custom Split on a '(' to extract everything after the '(':
Then after tidying the fields the table looks like this:
Step 2: Combine Wildlife
We now want to include the Wildlife table with our existing workflow. Before we combine the tables we need to ensure that the strings are encoded correctly so need to replace the ' with a '('. We can do this with a calculated field:
Name
REPLACE([Name],''',"'")
We are then ready to combine the two branches using an inner join on the Name field.
This then becomes our first output:
Stage Two
Step 3: San Diego Climate
Next we want to input the San Diego Climate table and then use an aggregation step to create a single row of the temp ranges. For this we can utilise the Min & Max values within the aggregation:
We can then append this row of data to our original workflow but first we need to make some changes to that table.
So that we can focus on each habitat, we need to spilt out each type onto a separate row. We can do this by utilising the custom split functionality again to split out all values with a ','.
We can then pivot the split fields using a columns to rows pivot. Within the setup you can use a 'Wildcard Pivot' on '-' to automatically bring all of the split fields through.
After the pivot we want to remove any blank fields and rename to Habitat split. The table should look like this:
To append two branches we need to create a dummy field in each, we use the number 1, and then join the tables on this dummy field.
We should now have a table with the high and low temperature on each row.
Step 4: Habitat Estimates
We can now combine the Habitat (estimates) table but first we need to make sure it is in a suitable format to join with our existing workflow.
First we can remove some fields about Elevation and Rainfall, then clean the Min Temp fields so it only contains the numeric values for the temperature in Fahrenheit. We can do this by splitting the field from the first space and only leaving the first number and changing that to a whole number data type.
We can then repeat the same process to identify the Max temp as well.
Then we are ready to join this to our existing workflow using an inner join on Habitat and Habitat Split
After the join we want to ensure there's only one row per wildlife species so using an aggregation step to group by Wildlife, Name, Status, Habitat-1, Class, Region, High F, Low F and bring through the Min for Min Temp (F) and the Max for Max Temp (F):
Our table should now look like this:
Step 5: Temperature Notes
We now want to create 2 calculated fields for 'Habitat Notes' and 'Degrees outside ideal'.
Degrees outside Ideal
IF [Low °F]<[Min Temp (F)]
THEN [Low °F]-[Min Temp (F)]
ELSEIF [High °F]>[Max Temp (F)]
THEN [High °F]-[Max Temp (F)]
ELSE 0
END
Then we can remove the min/max temp fields from the view.
Habitat Notes
IF [Degrees outside Ideal]=0
THEN "Ideal"
ELSEIF [Degrees outside Ideal]<0
THEN "Below"
ELSE "Above"
END
Step 6: Care Priority
The final steps for the challenge is to ensure that the priority for each animal is rated for the staff to use.
First we need to join the Care Priority table only our existing workflow. For this we want to just a left (or right) join to include all of the rows from our existing workflow with the matching rows from the Care Priority table.
We can then replace any Null values with the number 6 and filter to remove any Habitats that have scored 'Ideal' in the Habitat Notes field.
Then we want to rank the remaining values but we want to use the Absolute value for the Degrees outside ideal field so need to wrap it in an ABS() function before ranking the values.
Priority Order
Then we can clean the priority order by making any values, where the status is null, also null.
Priority Order
IF ISNULL([Status])
THEN NULL
ELSE [Priority Order]
END
We can then remove any unneeded fields and the final output should look like this:
You can view the 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!
