Using Alteryx to identify Top Level Manager

[hubyh]

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 Identifier	Name	Reports To	Line Detail 1
ID_Firstname_ Lastname	Firstname_Lastname	ID_Firstname_Lastname	Chief 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?

[JBLove]

@hubyh  - You might try looking at this Weekly Challenge problem, the solution deals with HR hierarchy data like this: Challenge #12: Creating an HR Hierarchy - Alteryx Community

[terry10]

@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.

  

[Raj]

@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.

[hubyh]

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.