Hierarchies in QlikView




The purpose of this post is to provide some permanent notes we can refer to about hierarchies in general and more specifically about the Hierarchy() and HierarchyBelongsTo() loads in QlikView.  I'm going to use some terms related to Tree data structures without defining them.  Google any unfamiliar terms to get a better understanding.

We deal with hierarchies in analytics and QlikView all the time. Some hierarchies are implied, like the ubiquitous calendar with Year->Quarter->Month->Day, etc.  In the case of the calendar, the hierarchy has levels that we all recognize and is typically stored in a denormalized table with a row for each value at the lowest level (say Day) and a column with repeating values for each higher level in the calendar hierarchy (Month, Quarter, Year, etc.).

Sometimes hierarchies are stored in a more generic structure called an adjacency list.  This is a structure where each node in the list is stored once and there is another "linking" field in the table pointing to the node's parent.  This structure is good for maintaining unbalanced (varying depth) or ragged (levels skipped) hierarchies.  It is easy to maintain but more difficult to report on because traversing the hierarchy requires recursive logic.  Consider this organizational structure as an example we will continue to develop in this post.

Figure 1. Adjacency List Table

hierarchy-adjacency-list

Thankfully, QlikView provides two types of loads, Hierarchy() and HierarchyBelongsTo(), that do all of the heavy lifting for us.  For the remainder of this post I'll focus on the syntax and examples for these loads.

The Hierarchy() load will take a table stored in an adjacency list (see Figure 1.) and create an expanded table that has a field for each level in the hierarchy.  This expanded table is ideal for pivot tables and drill groups.  It will also optionally create fields for the node path and depth.  The node path is a concatenated string, using your supplied delimiter, with all of the levels for a node.

Here is the syntax for the Hierarchy() load and an example.

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

NOTE: the parameters in square brackets ( [] ) are optional.

NodeID - name of the field that contains the node id. This field must exist in the input table.

ParentID - name of the field that contains the node id of the parent node. This field must exist in the input table.

NodeName - name of the field that contains the name of the node. This field must exist in the input table.

ParentName - a string used to name the new ParentName field. If omitted, this field will not be created.  If the field name has spaces or special characters then it needs to be in brackets or single quotes.

PathSource - name of the field that contains the name of the node used to build the node path. Optional parameter. The QlikView documentation says, "If omitted, NodeName will be used.".  I may be doing it wrong, but my experience is that if this value is omitted than the path field is not created (QV11.0 SR2).

PathName - 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.  If the field name has spaces or special characters then it needs to be in brackets or single quotes.

PathDelimiter - a string used as delimiter in the new Path field. Optional parameter. If omitted, ‘/’ will be used.  If a single character is provided then the parameter value does not need to be in single quotes, otherwise, it does.

Depth - 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.   If the field name has spaces or special characters then it needs to be in brackets or single quotes.

Here is an example of the Hierarchy() load in action with an example of the load statement based on the table in Figure 1. and the resulting table.  The result is a table with the same number of rows as the original adjacency list but with additional fields for each level in the hierarchy.

Figure 2. Hierarchy() load example

hierarchy-load-statement

Figure 3. Hierarchy with expanded nodes

hierarchy-expanded-nodes

The Hierarchy() load is great for creating a table that can be used to drive pivot tables or drill groups but it isn't very friendly for searching or selecting because the ancestors or a node are in separate fields.  This is where the HierarchyBelongsTo() load comes in.  It also loads from the adjacency list table.  It creates a table with all of the ancestors of a node in a single column.  In most cases the result of HierarchyBelongsTo() will have more rows than the original hierarchy table.

Here is the syntax for the HierarchyBelongsTo() load and an example.

HierarchyBelongsTo( NodeID, ParentID, NodeName, AncestorID, AncestorName, [DepthDiff] ) ( loadstatement | selectstatement )

NOTE: the parameters in square brackets ( [] ) are optional.

NodeID - the name of the field that contains the node id. This field must exist in the input table.

ParentID - the name of the field that contains the node id of the parent node. This field must exist in the input table.

NodeName - the name of the field that contains the name of the node. This field must exist in the input table.

AncestorID - a string used to name the new ancestor id field, which contains the id of the ancestor node.

AncestorName - a string used to name the new ancestor field, which contains the name of the ancestor node.

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

Here is an example of the HierarchyBelongsTo() load with an example of the load statement based on the table in Figure 1. and the resulting table. One example of how this table might be used would be associating a Section Access table to the Ancestor ID/Name level to give a user access to all employees' data who are below him in the hierarchy.

Figure 4. HierarchyBelongsTo() load example

hierarchybelongsto-load-statement
Note the Drop Field and Drop Table statements.  I like to drop the NodeName field, in this case EmployeeName, from the Ancestors table.  I also drop the original adjacency list table.  The result is a table and data model that looks like this.

Figure 5. Hierarchy with ancestor nodes

hierarchybelongsto-ancestor-nodes

Figure 6. Final data model

hierarchy-data-model

To see the benefit of having both tables add list boxes for EmployeeName and AncestorName.  When you select an EmployeeName from the EmployeeName list box only that one employee is now possible.  When you select the same employee from the AncestorName list box that employee and all of their ancestors are possible.  Refer to the following three screenshots for an example.

Figure 7. List boxes and table with no selections

hierarchy-list-box-and-table-no-selections

Figure 8. List boxes and table with EmployeeName selection

hierarchy-list-box-and-table-employeename-selection

Figure 9. List boxes and table with AncestorName selection

hierarchy-list-box-and-table-ancestorname-selection


TAGS: QlikView, Data Model, Load Script

Related News

IS YOUR COMPANY UPGRADING TO QLIKVIEW 12?

Axis Group was approached by a client asking for help...

Read More

Part 1 of this series describes what Qlik Sense does better than QlikView, from the perspective...

Read More