Hierarchy Function in QlikView

There are many instances we see hierarchical data in tables with self-referential integrity constraint. If we want to see the data with levels, we have to issue complex SQL’s and call them from Qlikview script. This needs good knowledge in writing the SQL’s.   

Qlikview has a function called - Hierarchy, which is used to display the data in hierarchical manner. This is possible only when there is self-referential integrity data.  Following is the syntax of Hierarchy machine.

Hierarchy

 

The hierarchy prefix is used to transform a hierarchy table to a table that is useful in a Qlik Sense data model. It can be put in front of a LOAD or a SELECT statement and will use the result of the loading

statement as input for a table transformation.

 

Hierarchy  (NodeID,  ParentID,  NodeName,  [ParentName],  [PathSource], [PathName],  [PathDelimi         ter],  [Depth])(load_statement  | select_statement)

I have taken EMP and DEPT tables from Oracle’s demo database for explaining hierarchy function. Following screenshot shows the data in both the tables.  You can see there is an self-referential integrity constraint between EMPNO and MGR columns. MGR column has the EMPNO of  the respective manager. This column (MGR) is referring EMPNO column. EMPNO acts as Parent and MGR as Child.

Following are the steps involved in this.

1. Open and create the script in Qlikview. Create an OLEDB connection to connect to Oracle schema.

1. Import EMP table from Oracle Schema.

1. Call Hierarchy function and pass the parameters as follows.
    
   Hierarchy(EMPNO,MGR,ENAME,MGR_NAME,ENAME,HIERARCHICAL_PATH, '-->', 'Level')
   Arguments:
   1^st Argument : Node  à EMPNO
   2^nd Argument : Parent_Key à MGR
   3^rd Argument : Node_Name à ENAME
   4^th Argument : Parent_Name à MGR_NAME
   5^th Argument : Path_Source à ENAME
   6^th Argument : Path_Name à HIERARCHICAL_PATH
   7^th Argument : Path_Delimiter à  '-->'
   8^th Argument :  Depth à 'Level'
    

1. I have derived a column which calculates total salary.
   SAL+IF(IsNull(COMM),0,COMM) as Total_Sal

1. Import DEPT table from database just to get DNAME by joining them.

1. Following is a sample table box which shows the employee names in the order by levels. Qlikview appends number in sequence to the column we are defining. Following has the hierarchical path (with à as separator)  and also the level which we have derived through Hierarchy function.

 Following is the output if we want to see in the order by level.

Your comments and suggestions are highly appreciated.

Happy Learning.