What is Hierarchy() ?

nikhilgarg — Thu, 19 Mar 2015 13:32:28 GMT

Hello,

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

tHanks

Re: What is Hierarchy() ?

Gabriel — Thu, 19 Mar 2015 13:35:35 GMT

Hi,

petter — Thu, 19 Mar 2015 13:46:10 GMT

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 — Thu, 19 Mar 2015 13:48:40 GMT

If you have this:

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.

thiago_mlg — Thu, 19 Mar 2015 14:03:09 GMT

NikHil, look my Document about it.

It is very detailed.

Thiago Gonçalo