transforming a hierarchy in to a flat file

[Kirstin]

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

[JohnJPS]

(oops... admins, please delete) 

[JohnJPS · Accepted solution]

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

[Kirstin]

Thanks @JohnJPS!

You are a lifesaver!  would have taken me ages to figure this out.

Kirstin

[fnoahbiro]

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

[JohnJPS]

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.