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:
|"Estimate"||"Estimate Copied From"||"Price"|
Note: No of iterations is not fixed
|Final Sales Order||Estimate||Price|
I want report output as follows:
|Sales Order||Discount Given|
|4040||(1000-750) = 250|
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)
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:
Hierarchy (Estimate, Estimate_Copied_from, Price,,,,,'Level') LOAD Distinct Estimate, Estimate_Copied_from, Price
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)