Macro

[BRRLL99]

Hi Team,

I have following data , for Each Employee Hierarchy of Managers need to be build , I have build this using multiple joints, I'm looking for Macro Solution

Input:  

ID	NAME	LEVEL	MANAGER ID	Manger NAME
101	SAM	6	108	TAYLOR
102	JOE	6	108	TAYLOR
108	TAYLOR	4	121	KRISTINE
110	RAH	4	121	KRISTINE
121	KRISTINE	3	111	GADE
111	GADE	MD
199	ROCKY	7	177	BLAKE
177	BLAKE	6	188	JUSTIN
188	JUSTIN	5	108	JASON
108	JASON	4	123	TIM
123	TIM	3	143	JIM
143	JIM	2	111	GADE

Expected Output : I have attached the solution , Looking for Macro Solution
Note : there is no rule that Employee will be reporting to next level Manager he can directly report to MD as well

ID	NAME	LEVEL	MANAGER ID	Manger NAME	Manager_LEVEL
177	BLAKE	6	188	JUSTIN	5
177	BLAKE	6	108	JASON	4
177	BLAKE	6	123	TIM	3
177	BLAKE	6	143	JIM	2
177	BLAKE	6	111	GADE	MD
108	JASON	4	123	TIM	3
108	JASON	4	143	JIM	2
108	JASON	4	111	GADE	MD
143	JIM	2	111	GADE	MD
102	JOE	6	108	TAYLOR	4
102	JOE	6	121	KRISTINE	3
102	JOE	6	111	GADE	MD
188	JUSTIN	5	108	JASON	4
188	JUSTIN	5	123	TIM	3
188	JUSTIN	5	143	JIM	2
188	JUSTIN	5	111	GADE	MD
121	KRISTINE	3	111	GADE	MD
110	RAH	4	121	KRISTINE	3
110	RAH	4	111	GADE	MD
199	ROCKY	7	177	BLAKE	6
199	ROCKY	7	188	JUSTIN	5
199	ROCKY	7	108	JASON	4
199	ROCKY	7	123	TIM	3
199	ROCKY	7	143	JIM	2
199	ROCKY	7	111	GADE	MD
101	SAM	6	108	TAYLOR	4
101	SAM	6	121	KRISTINE	3
101	SAM	6	111	GADE	MD
108	TAYLOR	4	121	KRISTINE	3
108	TAYLOR	4	111	GADE	MD

[OTrieger]

@BRRLL99 

I do not think that a macro is needed, you should be able to do it with Find and Replace tool, appending the manager Level from the employee level list.

All you need to do is connect the same data to a Find and Replace tool, then connect the same data source to a Unique tool to get unique entries and then connect it to the Find and Replace tool and append the level as a manager level.

[OTrieger]

[BRRLL99]

@OTrieger 

As shown in the expected output , I need entire Hierarchy of Reporting Managers

199	ROCKY	7	177	BLAKE	6
199	ROCKY	7	188	JUSTIN	5
199	ROCKY	7	108	JASON	4
199	ROCKY	7	123	TIM	3
199	ROCKY	7	143	JIM	2
199	ROCKY	7	111	GADE	MD

Even though Rocky reports to Blake ,
But Blake reports to Justin and so on

As you can see in this challenge : https://community.alteryx.com/t5/Weekly-Challenges/Challenge-12-Creating-an-HR-Hierarchy/td-p/36740

[Carolyn]

@BRRLL99 - If you're wanting a macro like what's used to solve that Weekly Challenge, can you just modify the macro from the Weekly Challenge solution? Or use the way the Weekly Challenge was solved to guide you for solving yours? 

[BRRLL99]

@Carolyn 

Hi
I have attached the modified macro which i build , it seems to generate duplicate records
Please look into it 

[Qiu · Accepted solution]

@BRRLL99 
I would recommend to use this hierarchy generation macro, and I have made some further processing to the hierarchy you required.

Noted that it appears that there is duplicated Names for ID 108 so have modified the ID of JASON to 109.