Iterative macro for non-standard org hierarchy

Question

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_NMEMP_IDREPORTS_TO_NAMEREPORTS_TO_IDJob TitleRankAnalyst, Name001234Supervisor, Name001236Analyst1Analyst, Second001237Manager, Second012347Analyst1Team Lead, Name001235Manager, Name012345Team Lead1Supervisor, Name001236Sr Manager, Name012346Supervisor1Manager, Name012345MD, Name223456Manager5Manager, Second012347Director, Name123456Manager5Sr Manager, Name012346Director, Name123456Sr Manager5Director, Name123456MD, Name212345Director7MD, Name212345VP, Name323456MD8VP, Name323456SVP, Name423456VP9SVP, Name423456  SVP9

OUTPUT:

EMP_NMEMP_IDREPORTS_TO_NAMEREPORTS_TO_IDJob TitleRankManagerManager_IDDirectorDirector_IDMDMD_IDVPVP_IDAnalyst, Name001234Supervisor, Name001236Analyst1Sr Manager, Name012346Director, Name123456MD, Name212345VP, Name323456Analyst, Second001237Manager, Second012347Analyst1Manager, Second012347Director, Name123456MD, Name212345VP, Name323456Team Lead, Name001235Manager, Name012345Team Lead1Manager, Name012345MD, Name223456MD, Name223456VP, Name323456Supervisor, Name001236Sr Manager, Name012346Supervisor1Sr Manager, Name012346Director, Name123456MD, Name212345VP, Name323456Manager, Name012345MD, Name223456Manager5MD, Name223456MD, Name223456MD, Name223456VP, Name323456Manager, Second012347Director, Name123456Manager5Director, Name123456Director, Name123456MD, Name223456VP, Name323456Sr Manager, Name012346Director, Name123456Sr Manager5Director, Name123456Director, Name123456MD, Name223456VP, Name323456Director, Name123456MD, Name212345Director7MD, Name223456MD, Name223456MD, Name223456VP, Name323456MD, Name212345VP, Name323456MD8VP, Name323456VP, Name323456VP, Name323456VP, Name323456VP, Name323456SVP, Name423456VP9SVP, Name423456SVP, Name423456SVP, Name423456SVP, Name423456

Macro Iteration Results.xlsx

182129 · Answer

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.

KGT · Answer

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.