6 Replies Latest reply: Oct 6, 2016 5:02 PM by Nikita Naresh RSS

    Hierarchy function creates a blank Level 1 column

    Nikita Naresh

      Hi,

       

      I need to create an employee-manager hierarchy such that the CEO is at level 1 followed by her direct reports in level 2, their direct reports in level 3 and so on. In my source file, I have the fields:

      Full Name

      ID

      Manager

      Manager ID

       

      I've tried to force a null manager ID for the CEO, but that makes no difference. Whether I keep the if ID='F0070' statement or not, I end up getting a blank Hierarchy level 1, with the CEO on level 2.

       

      My script is as follows:

      Hierarchy_Nodes:

      Load

      Distinct(ID),

      If(ID='F0070',null(),[Manager ID]) as [Manager ID],

      [Full Name] as [Hierarchy Level ]

      Resident Person;

       

      Hierarchy_tmp:

      Hierarchy(ID,[Manager ID],[Hierarchy Level ],'Manager',[Hierarchy Level ],'Tree','|','Level')

      Load

      Distinct(ID),

      [Manager ID],

      [Hierarchy Level ]

      Resident Hierarchy_Nodes;

       

      It would be great if you could explain why this is happening along with any suggested fixes. Thanks!