Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

New Column Creation

I want to create a new column based on the following logic:   this is how my dataset looks: Id -> Emp Name's ID Emp Name -> Employee Name Mgr Name -> The Employees Manager Apart from thes...
Need Help
Show and Tell
  • Nathaniel_C's avatar
    Nathaniel_C
    7 years ago

    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:

    Nathaniel_C 

v-diye-msft's avatar
v-diye-msft
Community Support
7 years ago

Hi Anonymous ,

 

If above suggestion provided by Nathaniel_C does work, please kindly mark it as solution to help others find it more quickly. thanks!