Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
Can any one tell me the use of Hierarchy() with an example. In help content i cannot guess much about it.
tHanks
Hierarchies is an excellent example with data from Henric Cronström
Unbalanced, n-level hierarchies is an even more extensive treament of the subject.
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.