Forum Discussion
New Column Creation
Anonymous ,
SelectEmpCombo = IF(EmpLevel[LEVEL]=1,LOOKUPVALUE(EmpLevel[EmpCombo],EmpLevel[Emp Name],EmpLevel[Mgr Name]))EmpCombo = CONCATENATE(EmpLevel[ID], CONCATENATE("," ,EmpLevel[Emp Name]))FindDelimiter = IF(EmpLevel[SelectEmpCombo]<> "",Find(",",EmpLevel[SelectEmpCombo]))ParentID = IF(EmpLevel[SelectEmpCombo]<>"",LEFT(EmpLevel[SelectEmpCombo],EmpLevel[FindDelimiter]-1))This should do it, although you may want to combine the columns. I left them separate for ease in debugging. First we combine the two columns [ID] and [Emp Name] into [EmpCombo] separated by ",". (This gives us a unique identifier for all. )Then we use IF() and LOOKUPVALUE () in [SelectEmpCombo. That gives us the answer, but then we need to separate out the Emp ID.
So, we use IF() and FIND() in [FindDelimiter] to find how many characters the ID is. Finally we use IF() and LEFT() to get the ParentID.
If this solves your issue, please consider marking it Solved. KUDOS are nice too!:smileyhappy:
Anonymous ,
This should do it, although you may want to combine the columns. I left them separate for ease in debugging. First we combine the two columns [ID] and [Emp Name] into [EmpCombo] separated by ",". (This gives us a unique identifier for all. )Then we use IF() and LOOKUPVALUE () in [SelectEmpCombo. That gives us the answer, but then we need to separate out the Emp ID.
So, we use IF() and FIND() in [FindDelimiter] to find how many characters the ID is. Finally we use IF() and LEFT() to get the ParentID.
If this solves your issue, please consider marking it Solved. KUDOS are nice too!:smileyhappy:
Nathaniel_C Thank you so much for the follow up! I just did a small change; I lloked up the ID of the Emp with Emp Combo and Manager Combo (Created Manager Combo = Manager ID + Manager Name). Then this worked for me!
Parent_ID = if(Table[Level]=1, lookupvalue(Table[ID], Table[EmpCombo], Table[ManagerCombo]))