Hierarchy feature in QlikView

We often deal with hierarchies in Business Intelligence tools. We have to transform the data and establish parent-child relationship between entities before we can use it for reporting.

QlikView has its own way of dealing with hierarchies using the hierarchy keyword. Let’s look at the below example.

I have a QVD file that has client proposals data. Various proposal versions can be created. My aim is to find out the discount given in Sales Order starting from base proposal. The data in QVD files is as follows:

QVD1

Note: No of iterations is not fixed

QVD2

I want report output as follows:

Now here, I need to establish parent-child relation between all the estimates so that we can find out the difference from the original estimate.

The syntax is:

Hierarchy (NodeID, ParentID, NodeName, [ParentName], [PathSource],[PathName], [PathDelimiter], [Depth]) (loadstatement | selectstatement)

Where

NodeID is the name of the field that contains the node id

ParentID is the name of the field that contains the node id of the parent node

NodeName is the name of the field that contains the name of the node

ParentName is a string used to name the new ParentName field. If omitted, this field will not be created

PathSource is the name of the field that contains the name of the node used to build the node path. Optional parameter. If omitted, NodeName will be used

PathName is a string used to name the new Path field, which contains the path from the root to the node.

Optional parameter. If omitted, this field will not be created

PathDelimiter is a string used as delimiter in the new Path field. Optional parameter. If omitted, ‘/’ will be used

Depth is a string used to name the new Depth field, which contains the depth of the node in the hierarchy. Optional parameter. If omitted, this field will not be created

Hierarchy statement in my script looks something like below:


FinalTable:
Hierarchy (Estimate, Estimate_Copied_from, Price,,,,,'Level') LOAD Distinct Estimate, Estimate_Copied_from, Price
Resident QVD1;


This generates multiple fields Price1, Price2, Price3 etc. to the required depth, with Price1 the top of the hierarchy.

Now all I need to do in my Chart expression is just (Price1 – Price)