Determining A Line Item By Reference to Other Line Items

phillip.catterall
phillip.catterall
in Modeling

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):

P_PERIOD_NAMEEMPLOYEE_NUMBERMANAGER_NUMBER
Apr-201863134666
Apr-20466117845
Apr-202554113009
Apr-204935547465
Apr-202922221253
Apr-20596043499

 

 

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?

Tagged:

Best Answer

  • M.Kierepka
    M.Kierepka
    Answer ✓
    Hi,
    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.

Comments

  • JensitSebastian
    JensitSebastian

    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. 

    Thanks,

    Jensit

  • phillip.catterall
    phillip.catterall

    Perfect, thank you! Did exactly what I needed.

  • phillip.catterall
    phillip.catterall

    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.

Categories