Determining A Line Item By Reference to Other Line Items


Hi all,


I'm trying to do something that I think SHOULD work but I can't get it quite straight in my head so I'd appreciate some support.


I've got a module that's holding employee data, dimensioned by Employee ID and by month. I need to be able to identify people managers, but the data extract doesn't have a flag for that. What I DO have is a line item that indicates each employee's line manager's employee ID like this (all employee numbers are randomised here):




What I'm trying to do, I guess, is the equivalent of an excel vlookup to find an item in Manager_number that matches the Employee_Number so I can have a boolean that shows as true if the employee's ID is in the manager number line item anywhere, but so far none of the lookups I've attempted have worked as I want them to. 


Can anyone give me a steer in the right direction?


Best Answer

  • M.Kierepka
    I assume Employee_number and Manager_number columns have "Employee" list format (if not, use FINDITEM and produce such columns). Then:
    1. Add line item "F_TRUE" with format as Boolean and formula as "TRUE".
    2. Add line item "IS Manager?" with format Boolean and formula: 'F_TRUE'[ANY:'Manager_number']. This is your result - it will indicate if this employee is in manager column anywhere on this list.


  • Hi  @phillip.catterall,


    To quickly identify the managers I will suggest the following: 

    1. I am assuming you would have a Manager's list in the Model. If not create  a List  called Manager with the codes. 

    2. Add line item say MGR with format as Manager list. Write formula FINDITEM( Manager, EMPLOYEE_NUMBER) , this function will find out if these employees are part of manager list. The EMPLOYEE_NUMBER should be in text format for function to work. 

    3. If you wish to flag it with Boolean pls use ISNOTBLANK(MGR) . This will give a TRUE where employees are managers. 


    Hope this helps. 



  • Perfect, thank you! Did exactly what I needed.

  • Thanks! I ended up going with the other solution just because people can change their management status from month to month and I need each month's data to reflect that; if I'd gone with a manager list all the months would be reflective of whatever was in that list rather than each month being calculated seperately.