ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Producing a denormalised output from Hierarchial Data

sandeep_chayanam
8 - Asteroid

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
kane_glendenning
10 - Fireball

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

sandeep_chayanam
8 - Asteroid

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
13 - Pulsar
13 - Pulsar

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.

AdamR
Alteryx
Alteryx

Hi Sandeep,

 

The iterative solution looks like this

 

HierarchyIterative.png

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.

Adam Riley
Principal Software Engineer
Tech Lead Core Engines, Alteryx
AdamR
Alteryx
Alteryx

Sorry attached the module instead of the package.

 

Trying again.

Adam Riley
Principal Software Engineer
Tech Lead Core Engines, Alteryx
sandeep_chayanam
8 - Asteroid

Thank you all for the ideas..

Thanks to Adam Riley for providing the actual iterative macro!

 

Regards,

Sandeep.

Rohit_Bajaj
9 - Comet

Thanks a lot Adam for posting this solution. I was able to customize this to give me the root parent for any child.

chandramouli1
7 - Meteor

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

RodL
Alteryx Alumni (Retired)

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". Smiley Wink

Labels