Hi all,
I was wondering if any of you have come accross the following use case and successfully implemented it in alteryx?
Scenario:
Input : Employee Referential with details of ManagerId (Manager is also an employee)
Output: Produce an output for each employee and all their direct/indirect subordinates
This can be easily achieved in SQL Server using hierarchyid data type / using Recursive CTE's.
I was wondering how to implement this in alteryx ? Perhaps using iterative macro?
for example:
Input:
EmployeeID | ManagerID |
1 | |
101 | 1 |
102 | 1 |
201 | 101 |
202 | 101 |
203 | 101 |
251 | 102 |
252 | 102 |
301 | 201 |
302 | 201 |
Output needs to be for each employee, produce a complete list of subordinates (both direct/indirect) as shown below:
EmployeeID | Direct/Indirect Subordinate IDs |
1 | 101 |
1 | 102 |
1 | 201 |
1 | 202 |
1 | 203 |
1 | 251 |
1 | 252 |
1 | 301 |
1 | 302 |
101 | 101 |
101 | 201 |
101 | 202 |
101 | 203 |
101 | 301 |
101 | 302 |
102 | 102 |
102 | 251 |
102 | 252 |
201 | 201 |
201 | 301 |
201 | 302 |
202 | 202 |
203 | 203 |
251 | 251 |
252 | 252 |
301 | 301 |
302 | 302 |
Solved! Go to Solution.
Hi Sandeep,
I would say that in most companies, you would probably have less than 15 levels to the hierachy and so you could just concatenate join tools to get this result with a transpose at the end.
The way this would work, is to run your input referential table into both sides of a Join tool, joining Employee ID to ManagerID, rename the new Employee ID to Level3 or similar, then Union the L&J outputs to make a Left Outer Join, Join this to the original Data on Level3 = ManagerID, rename, Union and repeat. You will end up with a table with Manager ID and then several columns for each of the levels under that, so transpose all the other fields, Filter Null() out, Sort and Unique. This doesn't have each employee linked to itself, and so you can add in EmployeeID joined to Employee ID as well, renamed and Unioned.
Sample Attached. You could turn this into a iterative macro, however you would need to play around with it, and you could proably knock this method out in 15 minutes or so.
Kane
Hi Kane,
Thanks for your reply.
I've provided employee-manager scenario just as an example.
My real use case follows the same pattern but has levels ranging upto 40-60 sometimes, hence I was looking for other alternative approaches (rather than using series of joins).
Thanks,
Sandeep.
Hi Sandeep,
I'm attaching a dummy case for your study. In this case, besides the use of an iterative macro, you'll see Append Fields dong big part of the job, which for what I understood, is the kind of approach you're looking for.
Append Fields, will append (duh!) the records from a source (S) to every record on the target (T).
If you bare the behaviour of the iterative macro, you'll find out that this Target input is your Universe.
Is a basic "all combinations set", but you'll find it useful as starting point for what you want to accomplish.
Best,
_AG_
PS: Thanks CailinS for the original idea/case/exercise.
Hi Sandeep,
The iterative solution looks like this
I've attached the module for you to see.
But basically it keeps getting the manger the next level up until it reaches the null indicating we are at the top, and then collates all of the results.
The macro input/ouput in the top left are how the records loop round.
The macro input in the bottom left is the lookup of everyone's direct manager.
and the macro output in the top right outputs the results everytime we step up a level.
Let me know if you have any questions on the technique.
Thank you all for the ideas..
Thanks to Adam Riley for providing the actual iterative macro!
Regards,
Sandeep.
Thanks a lot Adam for posting this solution. I was able to customize this to give me the root parent for any child.
Hi Adam,
I am looking for a workflow which helps me a create a employee-> manager 1 -> Manager 2 -> Manager 3 .....in seperate columns..What I have is Employee ID'S in one column and the corresponding manager ID'S in the next column..
Can you please help me with this?
Thanks,
Arun
Looks like you created your own post at http://community.alteryx.com/t5/Data-Preparation-Blending/Displaying-Hierarchy-of-Managers/m-p/34903...
You should be able to get the answer from what I posted there.
No real need to "double post".