Using Alteryx to identify Top Level Manager

Question

I have a data set that list all staff names and with their unique ID's  and who they report to with their immediate Line Managers unique ID

Unique IdentifierNameReports ToLine Detail 1ID_Firstname_ LastnameFirstname_LastnameID_Firstname_LastnameChief Executive Officer

I want to be able to use Alteryx to produce a workflow where I can enter a member of staffs name and Alteryx calculate their 2nd Level Line Manager.  Unique references for the 2nd Level line managers have an ID less than 25.  I think I need to use an Iterative macro but struggling with how to produce this.  Can anyone help?

hubyh · Answer

My current workflow starts with a Text input tool, in which I am adding the staff member for which I want to find their level 2 Line Manager.  So I would type in Fred Flintstone but his entry only returns his immediate line manager, Captain Caveman.  I want to ultimately be able to identify who his Level 2 Line manager is.  All Level 2 line managers have Unique Identifiers between 1_FirstName_LastName and 25_Firstname Line Manager.  As there is obviously a variance in the number of levels of command for each individual staff member, i thought an iterative macro may work.  But struggling to make it work. I have attached a sample data set below. Any help would be appreciated.

Sample Data.xlsx

Raj · Answer

@hubyh what does your input look like if it is similar to the added sample above you can use self join to achieve this while your data is in a single row as per designation you will require a iterative macro

add some sample data for more clarity.

terry10 · Answer

@hubyh

Sample data would really help us help you!

If you are only looking for 2nd level managers and not a full organizational hierarchy, you can do a simple append as shown here. Please accept this as a solution if it answers your question.

2nd Line Manager.yxmd