2022: Week 52 - Solution
Solution by Tom Prowse and you can download the workflow here.
It's our final challenge of HR month and also the final one of 2022! This week we are looking at employee hierarchies that trying to identify sub-departments for reporting and analysis.
Step 1 - Hierarchy Level
After inputting the Employees table, we want to look at finding the hierarchy level for each person. We can identify this by using the employee_id_hierarchy field by counting the number of pipes (|) within the field. This will identify how many 'levels' there are for the employee.
First, we want to duplicate the employee_id_hierarchy field and then remove any letters, numbers, and spaces from the string. This should leave us with just the |'s and we can then use this calculation to count how many there are:
Hierarchy Level
LEN([employee_id_hierarchy-1])-1
LEN([employee_id_hierarchy-1])-1
We use the -1 here as there is always an extra | at the start of the string.
Step 2 - Sub-Department Heads
Next we want to identify the sub-department heads, so we need to exclude any Executives, from the department field, and also use a wildcard match filter on title to remove any roles containing the word 'Administrator'.
We can then rank the hierarchy to find the 2nd-lowest and keep only that person.
First, we need to rank the levels:
Rank in Department
We can then use a filter to Keep-Only the 2 ranks and then remove the Rank in Department field.
Step 3 - Sub-Department Team ID
We now want to identify just the dependent team id for each of the sub-department heads. We can do this by splitting out the number after the first |. We can use the inbuilt split functionality but we can also write the calculation out manually:
subdept_team_id
TRIM( SPLIT( [dependent_team_ids], "|", 2 ) )
TRIM( SPLIT( [dependent_team_ids], "|", 2 ) )
Then we can keep only the subdept_team_id and employee_id fields and our table should look like this:
Step 4 - Join Sub-Dept & Employees
Now we can join the sub-departments back to the main employees table. First we need to create a dummy join field on both the sub-department list and the original employees table - we want these to be separate branches.
For the dummy join field, this can be anything but we are just going to use the number 1.
We can then use that dummy field as a joining field which will join all rows on both sides meaning that the data massively explodes in size:
To reduce the number of rows to something more accurate we can use a filter with this calculation:
CONTAINS([team_hierarchy], [subdept_team_id])
OR
CONTAINS([dependent_team_ids], [subdept_team_id])
This follows the requirements and only returns rows where the subdept_team_id is in the team_hierarchy or where the subdept_team_id is in the dependent_team_ids field. This should reduce the number of rows to 36 and the table should look like this:
We can then replace the ID with the actual team name by joining the Teams table as well. We can join these tables with subdept_team_id = team_id
We can then rename team_name-1 to subdept_name and then remove the team_id-1 field. We should now have the subdept_id and subdept_name is our table:
Step 5 - All Employees
We now need to make sure that all employees are included in the output table. To do this we want to join the workflow back to the first step where we identified the different hierarchy levels. We can use a left join to include all rows from the original step (hierarchy level earlier in the flow) and join on the employee_id fields.
We can then clean up the table and remove any fields that are not required for the output. Our final table 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 & @kelly_gilbert
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!
