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

Alteryx designer Discussions

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

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More
SOLVED

use of iterative macros to allocate levels in the data

Meteoroid

I have a dataset which has 2 fields "EmployeeID" and "ReportsToID". I am trying to allocate levels to each employee in the data based on at what level do they report to with respect to the level 0.

 

Example:

RowIDEmployeeIDManagerIDLevel
110010 
210021001 
310031001 
410041002 
510051002 
610061003 

 

A) I have created a workflow (1.yxmd) that finds the level 0 manager and sets the level 0 for all the employees reporting to manager (rows 1 in this table above).

 

RowIDEmployeeIDManagerIDLevel
1100100
210021001 
310031001 
410041002 
510051002 
610061003 


B) After this I created a macro that iterates through this data, filters for the employees reporting to the manager at max "level" in the data at this stage>> RowID 2 and 3. Macro name: "2.yxmc"

 

C) These records are passed to another macro that finds employees reporting to these employees (1002 and 1003) and allocates level=max_level+1. Macro name: "1.yxmc"

 

Macro (C) works fine but macro (B) is executing once only and hence not able to allocate teh levels to rest of the records. Any suggetsions/help on why this is not working?

Highlighted
Quasar
Quasar

How about the attached?

 

After removing the known level from your example data, the workflow adds the Level field with a value of 1 when the "ReportsTo ID" is 0, and then calls the macro.

 

The macro will take the iterative input, filter if Level is Null, joining those two streams together by "Employee ID" from the Left and "ReportsTo ID" from the Right, formula to add 1 to the known level, union all the data back together, and output all to the loop.

 

Using the Interface Designer view, I set the iterations to to stop at 20, if you had more than 20 levels, you would need to increase this.

report to level.png

Meteoroid

Thanks a lot for a very elegant solution.

Labels