topic Re: Hierarchy function creates a blank Level 1 column in QlikView

Hierarchy function creates a blank Level 1 column

nikita42 — Wed, 05 Oct 2016 09:52:21 GMT

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

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!

Re: Hierarchy function creates a blank Level 1 column

el_aprendiz111 — Wed, 05 Oct 2016 16:49:40 GMT

hi,

1 exapmple:

Re: Hierarchy function creates a blank Level 1 column

nikita42 — Wed, 05 Oct 2016 17:35:41 GMT

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..

Re: Hierarchy function creates a blank Level 1 column

el_aprendiz111 — Wed, 05 Oct 2016 19:16:59 GMT

hi,

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

Re: Hierarchy function creates a blank Level 1 column

nikita42 — Thu, 06 Oct 2016 08:40:19 GMT

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

Re: Hierarchy function creates a blank Level 1 column

el_aprendiz111 — Thu, 06 Oct 2016 20:55:08 GMT

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;

Re: Hierarchy function creates a blank Level 1 column

nikita42 — Thu, 06 Oct 2016 21:02:48 GMT

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.