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

Macros

Common Use Cases

Best Practices

Transformation

Join

Preparation

Accepted answers

AdamR_AYX

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.

HierarchyLookup.yxmd

AdamR_AYX

Sorry attached the module instead of the package.

Trying again.

HierarchyLookup.yxzp

All comments

kane_glendenning

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

EmployeeReferential Table.yxmd

sandeep_chayanam

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.

Aguisande

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.

AppendFields.yxmc

AdamR_AYX

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.

HierarchyLookup.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703