QV15 Hierarchy In QLIKVIEW

QLIKVIEW wow, a great tool again. Solve the big problem of hanling the hierarchical data.
We have two function at all, which can be used at script level
 - hierarchy(upto 8 variables can pass)
 - hierarchybelongsto( 6 variables can pass.)

both function can use in front of LOAD statement or SELECT.

hierarchy:
it convert the node adjacent table to , in that way each level of child record will written in separate field.

Lets do some handsome practice on table - scott.emp !!!!!!!!

create connection
load the table
below the script you direct copy paste to your script editor after adding connection, i have three set of queries we will see the behaviour of each set of queries one by one:

//HIERARChy(EMPNO,MGR,ENAME)
//LOAD EMPNO,
// ENAME,
// JOB,
// MGR,
// SAL;
//SQL SELECT *
//FROM SCOTT.EMP;

 :::
HIERARChy(EMPNO,MGR,ENAME)
it has three fields:
1. field1 i.e. empno - that has child records only
2. field2 i.e. mgr - reference to parent records
3. field3 i.e. ename - any column name can be used, depend on us what type of data we want to show in hierarchical manner.
 here i want to see the employee name in hierarchical order.


HIERARChy(EMPNO,MGR,ENAME,asdf,ENAME,[hierarchy_goeg], ';', 'HIERARCHY DEPTH')
LOAD EMPNO,
ENAME,
JOB,
MGR,
SAL;
SQL SELECT *
FROM SCOTT.EMP;

:::
HIERARChy(EMPNO,MGR,ENAME,asdf,ENAME,[hierarchy_goeg], ';', 'HIERARCHY DEPTH')it has three fields:
1. field1 i.e. empno - that has child records only
2. field2 i.e. mgr - reference to parent records
3. field3 i.e. ename - any column name can be used, depend on us what type of data we want to show in hierarchical manner.
 here i want to see the employee name in hierarchical order.
4. asdf - i use any dummy name .. either we can use the parent record description related field
5. ENAME - PathSource: The path in QlikView is a string containing one field per ancestor down to the node.
6. PathName: the name of the field that will contain the path.
7. Delimiter: the letter to separate the different fields
8. HIERARCHY DEPTH -the name of the field that will contain the depth of the node.





hierarchybelongsto:
The hierarchybelongsto prefix is used to transform a hierarchy table adjacent node table. Adjacent node table is the table where each record corresponds to a node and has a field that contains a reference to the parent node. (means table will store data in parent child relation help of two fields, parent record repeated over row untill all child has been written corespond to parent. see the below image first two field behaviour, it is a adjacent node table)
:::
HierarchyBelongsTo(EMPNO,MGR,ENAME, 'ANCESTORS_KEY','ANCESTORS_NAME', 'Depth')
it has three fields:
1. field1 i.e. empno - that has child records only
2. field2 i.e. mgr - reference to parent records
3. field3 i.e. ename - any column name can be used, depend on us what type of data we want to show in hierarchical manner.
 here i want to see the employee name in hierarchical order.
4. write parent record in field
5. write child record in field
6. Depth - depth number of record (0 for child node, summing up with 1 for roots)


geo2:
HierarchyBelongsTo(EMPNO,MGR,ENAME, 'ANCESTORS_KEY','ANCESTORS_NAME', 'Depth')
LOAD EMPNO,
ENAME,
JOB,
MGR,
SAL ;
SQL SELECT *
FROM SCOTT.EMP;