Hierarchy function creates a blank Level 1 column

[nikita42]

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!

[el_aprendiz111]

hi,

1 exapmple:

[nikita42]

Looking at the script, it looks like I've done the exact same thing, but I'm still getting a blank column before the highest parent..

[el_aprendiz111]

hi,

Would you be able to share a sample with the expected output?

[nikita42]

I've attached the qvw and 1 of the source files here.

If you look at the model, my CEO Mildred Mitchell shows up at hierarchy level 2 with a blank column under hierarchy level 1.

I need Mildred Mitchell to be at level 1.

Thanks

[el_aprendiz111]

Hi,

Directory;

PERSON:

LOAD ID,

     first_name,

     last_name,

     [Full Name],

     [Salary Grade],

     [Hire Date],

     [Employee/Contractor],

     [Payment Type],

     Vendor,

     [Cost Center],

     Manager,

     [Location ID],

     If(ID='F0070',[Full Name],Manager) as Manager_ll

FROM

BSE_WEB.xlsx

(ooxml, embedded labels, table is person);

[VIEW]:

LOAD

Distinct

ID             AS VALUE,

ID          & '-[ID]' AS NODE_ID,

[Full Name] &   '-[Full Name]'  AS PARENT_NODE_ID

Resident PERSON;

[VIEW]:

LOAD

Distinct

[Full Name]               AS VALUE,

[Full Name] & '-[Full Name]'  AS NODE_ID,

Manager_ll  &    '-[Manager_ll]' AS PARENT_NODE_ID

Resident PERSON;

[VIEW]:

LOAD

Distinct

Manager_ll             AS VALUE,

Manager_ll  &  '-[Manager_ll]'  AS NODE_ID,

Manager_ll  &  '-[Manager_ll]' AS PARENT_NODE_ID

Resident PERSON;

[HIER]:

Hierarchy(NODE_ID_H,PARENT_NODE_ID_H, ID, PARENT_NAME, NAME,MY_LEVEL)

LOAD

NODE_ID,

NODE_ID   AS NODE_ID_H,

PARENT_NODE_ID  AS PARENT_NODE_ID_H,

VALUE           AS ID,

VALUE           AS NAME

Resident VIEW;

DROP Table VIEW;

[nikita42]

Thanks for this. Could you please explain why you've done 3 different loads separately for the names, manager names, and manager IDs?

I don't understand why my initial code didn't work.