transforming a hierarchy in to a flat file
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Labels:
- Parse
- Preparation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
(oops... admins, please delete)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
