Hi,
I want to create a datasource to control access management. It should have a column per employee, and a column showing what that person is allowed to see.
Today I have an HR hierarchy where for each row it's one employee, then has separate columns for manager, head of department, VP, etc.
i.e. employee1 manager dept head
employee2 manager dept Head
How do I transform it so it has the format below? (open to suggestions if there is a better way)
i.e.
employee1 employee1
employee2 employee2
manager manager
manager employee1
manager employee2
dept head dept head
dept head manager
dept head employee1
dept head employee2
Many thanks!
K
Solved! Go to Solution.
(oops... admins, please delete)
Hi @Kirstin
You could try, essentially, a Transpose joined to itself. I added a few other tools to manage it, such as a formula to apply a "level" (e.g. so that employee < manager < head, etc), which allows us to eliminate unwanted rows after the join. Trying to describe all the details borders on futile, so here's a picture, and the attached (version 11) workflow runs your example successfully as well as a slightly more complex test example. Hope that helps!
- John
Hi John,
Thank you for your response. How would you unwind the transformation? That is, go from your output back to @Kirstin's input data.
Thanks!
-Noah
Hi @fnoahbiro,
You can reverse it using a CrossTab and Text-to-Columns along with a couple other tools:
The attached workflow shows this in-line with my earlier examples. You do need to adjust for the expected number of levels in the hierarchy, but hopefully that doesn't change much for any given dataset.