What is Hierarchy() ?

[nikhilgarg]

Hello,

Can any one tell me the use of Hierarchy() with an example. In help content i cannot guess much about it.

tHanks

[Gabriel]

Hi,

This resource might help http://community.qlik.com/docs/DOC-4823

[petter]

Hierarchies is an excellent example with data from Henric Cronström

Unbalanced, n-level hierarchies is an even more extensive treament of the subject.

[simospa]

If you have this:

NodeID	ParentID	Employee	Role
1		Brian	Global Manager
2	1	Paul	Regional Manager
3	1	Clare	Regional Manager
4	2	Stephen	Country Manager
5	3	John	Country Manager
6	3	David	Country Manager
7	4	Bernhard	Senior Sales
8	5	Mark	Senior Sales
9	6	Pamela	Senior Sales
10	6	Susan	Senior Sales
11	9	Sean	Junior Sales
12	9	Terry	Junior Sales

You can obtain a structure like this:

NodeID	ParentID	Employee	Role	ParentDesc	Path	Level
1		Brian	Global Manager		Brian	1
2	1	Paul	Regional Manager	Brian	Brian/Paul	2
3	1	Clare	Regional Manager	Brian	Brian/Clare	2
4	2	Stephen	Country Manager	Paul	Brian/Paul/Stephen	3
5	3	John	Country Manager	Clare	Brian/Clare/John	3
6	3	David	Country Manager	Clare	Brian/Clare/David	3
7	4	Bernhard	Senior Sales	Stephen	Brian/Paul/Stephen/Bernhard	4
8	5	Mark	Senior Sales	John	Brian/Clare/John/Mark	4
9	6	Pamela	Senior Sales	David	Brian/Clare/David/Pamela	4
10	6	Susan	Senior Sales	David	Brian/Clare/David/Susan	4
11	9	Sean	Junior Sales	Pamela	Brian/Clare/David/Pamela/Sean	5
12	9	Terry	Junior Sales	Pamela	Brian/Clare/David/Pamela/Terry	5

or, if you specify more parameters:

NodeID	ParentID	Employee	Role	ParentDesc	Employee1	Employee2	Employee3	Employee4	Employee5	Path	Level
1		Brian	Global Manager		Brian					Brian	1
2	1	Paul	Regional Manager	Brian	Brian	Paul				Brian/Paul	2
3	1	Clare	Regional Manager	Brian	Brian	Clare				Brian/Clare	2
4	2	Stephen	Country Manager	Paul	Brian	Paul	Stephen			Brian/Paul/Stephen	3
5	3	John	Country Manager	Clare	Brian	Clare	John			Brian/Clare/John	3
6	3	David	Country Manager	Clare	Brian	Clare	David			Brian/Clare/David	3
7	4	Bernhard	Senior Sales	Stephen	Brian	Paul	Stephen	Bernhard		Brian/Paul/Stephen/Bernhard	4
8	5	Mark	Senior Sales	John	Brian	Clare	John	Mark		Brian/Clare/John/Mark	4
9	6	Pamela	Senior Sales	David	Brian	Clare	David	Pamela		Brian/Clare/David/Pamela	4
10	6	Susan	Senior Sales	David	Brian	Clare	David	Susan		Brian/Clare/David/Susan	4
11	9	Sean	Junior Sales	Pamela	Brian	Clare	David	Pamela	Sean	Brian/Clare/David/Pamela/Sean	5
12	9	Terry	Junior Sales	Pamela	Brian	Clare	David	Pamela	Terry	Brian/Clare/David/Pamela/Terry	5

It's very useful to provide structure related to parent-child.

The script used for my example is:

Hier:

Hierarchy(NodeID, ParentID, Employee,

ParentDesc, Employee, Path, '/', Level)

LOAD NodeID,

     ParentID,

     Employee,

     Role

FROM

foo.xlsx

(ooxml, embedded labels, table is Sheet1);

You can use the wizard when importing a Table file (select the file, then click twice "Next").

You can refer to user guide to better understand the list of parameters.

Let us know.

S.

[thiago_mlg]

NikHil, look my Document about it.

HIERARCHY - DESCRIPTION

It is very detailed.

Thiago Gonçalo