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

P_PERIOD_NAME	EMPLOYEE_NUMBER	MANAGER_NUMBER
Apr-20	18631	34666
Apr-20	46611	7845
Apr-20	25541	13009
Apr-20	49355	47465
Apr-20	29222	21253
Apr-20	5960	43499

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?

Accepted answers

M.Kierepka

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.

All comments

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

M.Kierepka

phillip.catterall

Perfect, thank you! Did exactly what I needed.

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.

Quick Links

Can you help?

Unanswered questions

Request for Workflow Design: End-User Report Finalization & Stakeholder Notification with Version Co
Hi everyone, I'm looking to design a workflow and would appreciate your input or guidance on how to set this up effectively in Anaplan. So, the goal is: Once end users complete their data input, they should be able to trigger a process that: Notifies stakeholders via email with a link to a report. The report includes: A…
Generating Failure Dump - even though no warnings/error in Actions
I've few actions in our Supply model which when run takes some time on the "generating failure dump" step at the end of the action even though the import is a full success without warning or errors. This is just a few seconds when run for a single action, however we have a process with 120 actions, and most of these…
OAUTH 2.0 vs Google Sheets Extension
Hi, My org is looking to explore leveraging Google Apps script to extract data out of Anaplan and update already built templates in Google Sheets. We use the Anaplan Extension currently but find it crashes often with the amount of tabs, data, etc. Has anyone used both Anaplan OAUTH 2.0 API and Goolge Apps Script to write…