Determining A Line Item By Reference to Other Line Items
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?
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.2
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.1
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.0