Iterative macro for non-standard org hierarchy

[182129]

I've looked at several posts regarding hierarchy workflows but nothing seems to work for me. I have input that gives employee information including who they report to. I have ranked each position and what I need to do is iterate through the reports to field until I reach someone who is a level 5+ to assign a manager, level 7+ to assign their director, level 8+ for MD, and level 9+ for VP.

Unfortunately, the hierarchy doesn't just step up one time to get to the next level. Some level 1's report to a level 5, some report to another level 1, some managers report to a director and others report straight to an MD.

Below and attached is sample input/output on a very basic level. I'm assuming an iterative macro is the way to go but I'm having difficulty getting it to give me the outputs I want.

Oh and I should mention I'm on Alteryx 2023.2.1.173 Patch 4, as the macro configuration can be different than the newer versions.

INPUT:

EMP_NM	EMP_ID	REPORTS_TO_NAME	REPORTS_TO_ID	Job Title	Rank
Analyst, Name	001234	Supervisor, Name	001236	Analyst	1
Analyst, Second	001237	Manager, Second	012347	Analyst	1
Team Lead, Name	001235	Manager, Name	012345	Team Lead	1
Supervisor, Name	001236	Sr Manager, Name	012346	Supervisor	1
Manager, Name	012345	MD, Name	223456	Manager	5
Manager, Second	012347	Director, Name	123456	Manager	5
Sr Manager, Name	012346	Director, Name	123456	Sr Manager	5
Director, Name	123456	MD, Name	212345	Director	7
MD, Name	212345	VP, Name	323456	MD	8
VP, Name	323456	SVP, Name	423456	VP	9
SVP, Name	423456			SVP	9

OUTPUT:

EMP_NM	EMP_ID	REPORTS_TO_NAME	REPORTS_TO_ID	Job Title	Rank	Manager	Manager_ID	Director	Director_ID	MD	MD_ID	VP	VP_ID
Analyst, Name	001234	Supervisor, Name	001236	Analyst	1	Sr Manager, Name	012346	Director, Name	123456	MD, Name	212345	VP, Name	323456
Analyst, Second	001237	Manager, Second	012347	Analyst	1	Manager, Second	012347	Director, Name	123456	MD, Name	212345	VP, Name	323456
Team Lead, Name	001235	Manager, Name	012345	Team Lead	1	Manager, Name	012345	MD, Name	223456	MD, Name	223456	VP, Name	323456
Supervisor, Name	001236	Sr Manager, Name	012346	Supervisor	1	Sr Manager, Name	012346	Director, Name	123456	MD, Name	212345	VP, Name	323456
Manager, Name	012345	MD, Name	223456	Manager	5	MD, Name	223456	MD, Name	223456	MD, Name	223456	VP, Name	323456
Manager, Second	012347	Director, Name	123456	Manager	5	Director, Name	123456	Director, Name	123456	MD, Name	223456	VP, Name	323456
Sr Manager, Name	012346	Director, Name	123456	Sr Manager	5	Director, Name	123456	Director, Name	123456	MD, Name	223456	VP, Name	323456
Director, Name	123456	MD, Name	212345	Director	7	MD, Name	223456	MD, Name	223456	MD, Name	223456	VP, Name	323456
MD, Name	212345	VP, Name	323456	MD	8	VP, Name	323456	VP, Name	323456	VP, Name	323456	VP, Name	323456
VP, Name	323456	SVP, Name	423456	VP	9	SVP, Name	423456	SVP, Name	423456	SVP, Name	423456	SVP, Name	423456

[KGT]

Hi there,

I'm not sure why you think this is "non-standard". You mention you've read a couple of hierarchy posts, and so I assume (but not sure) you've read this: https://community.alteryx.com/t5/Engine-Works/Building-a-Hierarchy-With-Kevin-Bacon/ba-p/453715 and played with this: https://community.alteryx.com/t5/Community-Gallery/Hierarchy-Generation-Macro/ta-p/880709

I just put that macro on your data and the output provides every link from Employee up the chain until that runs out. In your data the max is 7 people for Emp 001234. You would then need to format accordingly.

Or are you looking to have the macro output the exact format you have shown? If that's the case, can you post what you've got so far and then we can help round it off.

[182129]

But I don't want every employee up the chain. I want the first Reports To up the chain that is a rank 5 and label it as manager with manager ID, then there could be a senior manager in between but I don't care so I want the next rank for Director, if there is no Director but there is an MD, put the MD for both director and MD. Etc.