Producing a denormalised output from Hierarchial Data

[sandeep_chayanam]

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

[chandramouli1]

Hi Rodl,

Sorry about that!

Thanks.

Arun

[RodL]

No worries.

I basically wanted to let Adam know that your question may have been resolved elsewhere.

[P007]

Hey AdamR,

I had an issue regarding your solution, though the solution works on a smaller data set, it runs into infinite loop (Like - 6200 iterations were run with 3249 iterative records remaining) when I apply on my data set. The data set which I am assuming it the same as above but which more rows in it like 300,000 rows of data.

Also How can we get loop in if we give an input for one of the employee ID manually to get all the subordinates under him direct or indirect ...