community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Recursive Macro

Meteoroid

Hi All,

 

I'm running into an issue that's a variation on what's already been solved (like an org chart) - but it's different enough that I can't seem to figure it out. 

 

I have a workflow that produces an output - a bunch of IDs and Names that have parent-child relationships, as follows: 

Parent Role IDParent Role NameChild Role IDChild Role Name
1234Role 12341111Role 1111
1234Role 12341112Role 1112
1235Role 12351113Role 1113
1112Role 11121115Role 1115
1236Role 12361234Role 1234

 

As you can see, sometimes a parent is only a parent, or a child is only a child. But sometimes a parent is also the child of another parent, or a child is the parent of another process, which is where it gets a bit complicated. You would solve this issue in SQL using a CTE recursive query, or some variation on that. 

 

It differs from the org chart hierarchy problems that have already been solved because in those examples, every employee has a manager except for the boss (CEO), so these have been solved using macros with joins that make their way down the entire org hierarchy.

 

I have been able to link the roles that have multiple levels (e.g. 1234 --> 1112 --> 1115), but my iterative macro ONLY catches those - it misses all the roles that only have one child, or anything else, because then there's nothing to join on. 

 

I want my output to look like this, with every role (no matter at what level in the hierarchy) to have all the child roles listed:

Parent Role IDParent Role NameChild Role IDChild Role Name
1234Role 12341111Role 1111
1234Role 12341112Role 1112
1234Role 12341115Role 1115
1235Role 12351113Role 1113
1112Role 11121115Role 1115
1236Role 12361234Role 1234
1236Role 12361111Role 1111
1236Role 12361112Role 1112
1236Role 12361115Role 1115

 

Like I said the current workflow I have outputs the first table, so including a workflow wouldn't help. I just need a macro to turn the first table into the second one. Any help is appreciated! 

 

Thanks

Reusing my answer from https://community.alteryx.com/t5/Alteryx-Designer-Discussions/perform-rollup-on-organizational-hiera...

 

2018-12-20_21-20-50.png

 

Basically an iterative macro starts with the leaf nodes and moves up level by level until all built into heirarchy

Meteoroid

@jdunkerley79

 

Is it possible for you to attach the macro for an older version on Alteryx? I'm unable to open it with my version of Alteryx - I'm on version 2018.3.5.52487, and cannot upgrade any time soon.

 

Thank you for the help. 

Highlighted

Attached.

 

Hopefully will work in 18.3

Labels