<img alt="" src="https://secure.leadforensics.com/150446.png " style="display:none;">

Hierarchy feature in QlikView

Anuradha Bankar Sep 05, 2012

business intelligence Qlikview BI Technology Business Intelligence Tools

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

"Estimate" "Estimate Copied From" "Price"
X - 1000
Y X 900
Z Y 800
Z11 Z 750

Note: No of iterations is not fixed

QVD2

Final Sales Order Estimate Price
4040 Z11 750

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)

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)

e-Zest is a leading digital innovation partner for enterprises and technology companies that utilizes emerging technologies for creating engaging customers experiences. Being a customer-focused and technology-driven company, it always helps clients in crafting holistic business value for their software development efforts. It offers software development and consulting services for cloud computing, enterprise mobility, big data and analytics, user experience and digital commerce.