looping through dataset

[kiteandwindsurfer]

I feel pretty comfortable with Alteryx (or at least I thought I was) - but in all honesty more comfortable in sql environment, so that is the approach I am taking with this algorithm (see: make hierarchy flowchart.pdf).   I am using Alteryx 10.5, have searched other related questions regarding "looping" on this forum but somehow am failing to grasp some of the concepts - so I guess putting me at starting from scratch.

thanks in advance for anyone who can shed some guidance!

Brian

[dataMack]

Check out the posts in the community about Iterative Macros.  I think those examples will be helpful with what your are trying to solve.

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/CS-Macro-Dev-Iterative-Macros/ta-p/10303

[kiteandwindsurfer]

thank you for the quick reply Jason, the example you cited does seem related however I am still at a dead end in terms of application to my project.  However following the "iterative macro" thread might eventually turn up something, I'll poke around there and see if I can get a grasp on direction.  Perhaps some of my issue is the difficulty in following the data flow as the macros in examples are only related as a graphical icon - not so easy to 'look inside'.

...but again much appreciation for your suggestion!

regards,

Brian

[SeanAdams]

Hey Brian,

I feel your pain - I came from a development background in declarative code and SQL, so I also grasp sometimes for how I'd do this in SQL :-).

Very keen to help out - easiest way for us to get there is if you can mock up some input data; and your desired outputs (like @JoeM does on the weekly challenges) and attach it to this thread as an Alterxy flow, we'll definitely tear into this.

:-) looking forward to this one, and we've got an entire weekend ahead to get you to a solution.

Cheers Brian

Sean

[kiteandwindsurfer]

Sean,

What a wonderful community within Alteryx, I just joined yesterday and feel so included, so regards to you as well as all the others who come across this missive.

OK, I've attached a sample data file (see: "Creating hierarchy with Alteryx data example.xlsx") with two tabs, first tab would be the raw data input and the second tab would be the eventual output.  I've realized the initial data flow chart was not inclusive of all the parts that will be necessary for the final result, but for me the start is creating the framework.

thanks again Sean as well as others who feel like rolling up their sleeves on this one!

many regards,

Brian

[SeanAdams]

Right - step 1 done - I've packaged this into an alteryx file in the same format that @JoeM uses so that it's relatively easy for anyone to tackle this.

Step 2 (solution) to follow:

[SeanAdams · Accepted solution]

Hey @kiteandwindsurfer

I've attached one solution to your problem - it works roughly like this:

- there's an iterative macro to find out the level of each employee

- There's a tile component that tells you your ID under your boss (i.e. everyone who reports to person X is numbered from 1 to n)

so the combination of level ID and employeeIDUnderDirect tells you exactly where to find someone on the tree.

To create all the Level columns - just take the level ID and transpose it

Finally  - stitch these 3 pieces together with a multi-join.

This appears to be working, and you can use this as a basis for any additional capability you need.    If this looks good - would you mind marking as solved, or else just let me know if there's additional pieces that still need work.

Cheers

Sean

[kiteandwindsurfer]

thank you Sean!  Now I go through and grasp what is going on in case some tweaks are indicated but many thanks to you!  Hopefully this will also be something for others moving forward.  

regards,

Brian