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 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Nathaniel_C  Hey thanks a lot! But could there be any reason why i am getting this error?

Nathaniel_C's avatar
Nathaniel_C
Icon for Community Champion rankCommunity Champion
7 years ago

Anonymous ,

I will be offline for a couple of hours, but I wonder if you might have two employees with the same name, and different ids? or a typo on entering the id. Then you would have multiple values for the same name.