Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Employee
Employee

Unbalanced, n-level hierarchies

Hierarchies are very common in all database and business intelligence solutions. Usually they are balanced and with a fix number of levels, and then they do not pose any problems. Just load the data, add a drill-down group, and you’re done.


Adjacent Notes source 2.png

But there is one type of hierarchy that is somewhat tricky to get right – an unbalanced, n-level hierarchy. Typical for this type of hierarchy is that the levels are not named, and you really don’t know on which level you need to search for a specific node.

Usually such a hierarchy is stored in an Adjacent Nodes table, i.e. a table that has one record per node and each node has a reference to its parent.

Such a table can be loaded into QlikView directly using the Hierarchy prefix. This prefix will transform the Adjacent Nodes table into an Expanded Nodes table that has additional fields that you can use in your app.

Data model single table - BP.png

With the fields in this table, you can easily create a pivot table and a tree-view list box. Below you can see some wine districts displayed in both these object types:

Tables - BP.png

One challenge with hierarchies is that you can refer to a node in two different ways: Either to the node including the entire sub-tree, or to the node only, excluding all sub-nodes. In the example with the wine districts, it would mean any wine from Bordeaux, and unspecified Bordeaux, respectively. In the pivot table above, the difference is obvious: Any wine from Bordeaux sums up to 150 units, and the unspecified Bordeaux sums up to 18 units.

A user usually wants to make selections referring to the entire sub-tree, but the above solution does not have any field for this. To create such a field, you need the second hierarchy-resolving prefix – the HierarchyBelongsTo.

This prefix will also transform the hierarchy table. The result will be a table containing one record per descendant-ancestor pair. In other words, the ancestor (tree ID) will link to all its descendants (node ID), and can thus be used to make selections of entire sub-trees. (The “TreeBridge” table in the picture below.)

But it doesn’t stop here… The above solution creates one field in which tree searches can be made, but in order to create a drill-down for trees, you need an additional table – an expanded nodes table for the trees. This can be created with a second Hierarchy statement, but now one that links to the tree ID instead of the node ID. (The “Trees” table in the picture below.)

Data model full - BP.png

The data model with the three hierarchy tables is the one I recommend: It generates all fields you need.

A more elaborate explanation with script examples can be found in the technical brief about Hierarchies.

HIC

Further reading related to this topic:

Authorization using a Hierarchy

Bill of Materials

44 Comments
rva_heldendaten
Contributor III

Hi!

Unfortunately HIERARCHY Load is not able to handle BillOfMaterials(BOMs) correctly.

If for example the same screw is used multiple times in a single BOM, Hierarchy Load does not generate all nodes correctly.

I once wrote a blog post about this issue (here) - hope your German is still good enough

Any plans to change this in the future? Currently I solve this through a recursive SQL statement.

Thx,

Roland

335 Views
Not applicable

Great reading once again!

I know it is a blog - But coding examples would be nice included in blog. I know it is possible to get the solution. But examples written could very nice!

335 Views
Not applicable

follow his link

0 Likes
335 Views
Employee
Employee

Roland

You're right that the Hierarchy prefix cannot resolve a BOM correctly, but I think there is a good reason for this: A Bill Of Materials is really not a dimensional hierarchy. First of all, as you point out, a node can have several parents. Secondly, the same node can exist many times in the tree and have different quantities, depending on where in the tree it is found: The nodes are instantiated and each instance (e.g. the screw in the wheel) has a number.

Thirdly, the total quantity (of the dimension "screw") should be calculated - it is the sum of the product of all the quantities of the instances above.

All this makes the BOM a transactional table rather than a dimensional.

I would load the BOM using a For-Next loop and some advanced scripting that does the above calculation. Maybe this is material for a blog post...

HIC

335 Views
Employee
Employee

@Roland Vecera

You should be able to load a BOM using the following (The quantities in "QuantityPerPath"):

BOM:

Hierarchy (Part, Parent, PartName, ParentName, NodeQuantity, QuantityPath, '/', Depth)

LOAD Part,

  Part as PartName,

  Parent,

  Text(Quantity) as NodeQuantity

FROM BOM.xlsx (ooxml, embedded labels, table is Sheet1);

Sums:

Load Exp(Sum(lnQuantity)) as QuantityPerPath,

  QuantityPath

  Group By QuantityPath;

Load Log(Subfield(QuantityPath,'/')) as lnQuantity,

  QuantityPath

  Resident BOM ;

// HIC

0 Likes
335 Views
Or
Valued Contributor II

Thank you, Henric. As always, your posts and comments are what keeps me reading QlikCommunity.

0 Likes
335 Views
olac
New Contributor III

Thank you for elaborating on this topic Hic, I have been struggling with this as well before deciding to go for SQL functions (CTE).

So letting go of HierarchyBelongsTo but rather use Hierarchy and then grouping on the path may be the solution. Impressive as usual.

As a future development suggestion I think I still agree with Roland, would be nice to have it all calculated in a tree level that supports non dimensional hierarchies (or branching out upwards in the hierarchy if you like), like common table expressions in sql.


Ola

335 Views
Not applicable

Henric, thanks for this great article. It comes right on time because I'm going to rework my assets datafile containing installations and components in an unbalanced hierarchy. If I would have studied the hierarchyBelongsTo a little bit sooner I could have saved myself a lot of trouble. Creating the 'trees' table is a great idea.

0 Likes
335 Views
Not applicable

Thanks Henric, for this great article. It comes right on time because while struggling with an unbalanced hierarchy. Creating the 'trees' table is a great idea. Would you have a sample demo app to share so we get an idea on scripting end on how to accomplish the 'trees' table.

Again thanks for sharing this idea, it was very helpful.

Thanks,

DD.

0 Likes
335 Views
Employee
Employee

A script example can be found on http://community.qlik.com/docs/DOC-5334

HIC

0 Likes
335 Views
Not applicable

Hi Henric,

Thanks for sharing the code link. Would be able to share how you can build the Districts pivot table with tree view which expands and collapses as shown by you at the top, that would be of great help to better understand the article.

Appreciate all your help.

Happy Thanks Giving!

Thanks,

DD

0 Likes
335 Views
Employee
Employee

The pivot table is easy to make:

  • Create a pivot table with Node1..Node7 as dimensions.
  • Make the dimension columns narrower.
  • On Properties - Style, check the "Indent Mode"
  • Also check the "Use only first dimension label"
  • Uncheck "Vertical Dimension Cell Borders"
  • On Properties - Presentation, check the "Subtotals on Top"

Done!

HIC

0 Likes
335 Views
Not applicable

Thanks Henric, will try to work with the suggested solution. Hope it works as planned.

Thanks,

DD

0 Likes
335 Views
Not applicable

Can you please share the qvw file that shows your sample example mentioned above, to get better idea on the hierarchy concept.

Thanks,

DD

0 Likes
335 Views
Not applicable

Hi Henric - Excellent technical brief!!

I'm dealing with a manufacturing bill of materials that is unbalanced, ragged and "multi-parented." That is, a sub-component can appear as the child in multiple, different parent components … and at different levels in the tree as well.

I believe it is the multi-parented aspect that causes the Hierarchy load and HierarchyBelongsTo to not work. (Actually, I have only proved HierarchyBelongsTo to not work, but I have not confirmed Hierarchy does work.) By "not work" I mean there are cases where a child component is not present in all branches.

Would you expect this to happen?

335 Views
Employee
Employee

Bill

We have had bugs in this area - bugs that made the Hierarchy statement not always respect multiple parents. This has been fixed in SR5. If you run the following script:

Hierarchy (Node, Parent, NodeName) Load Node, Node as NodeName, Parent inline

[Node, Parent

A,

B,

B,A

B,A

C,A

C,B];

you should get 8 records in your table, but in some older versions you only get 6.

RowCount.png

Further, a BoM is difficult to model. See Roland Vecera's question above and my answer to it.

HIC

0 Likes
335 Views
Not applicable

Henric, working on an interesting hierarchy question with regards to HR reporting.  I have snapshot data throughout the life of a company at yearly, quarterly, monthly and daily levels of granularity.  We plan to build an app combining the data at the month level.  The data reflects both active and terminated employees and we want to look at headcount over time starting off in relation to each manager's organization.  The issue I can see is that managers can change and employees can move (slowly changing dimensions?).  We have a flag to designate the granularity and date of snapshot. 

How would you recommend handling this type of changing structure so we can capture the information correctly? 

0 Likes
335 Views
Employee
Employee

I am not sure that this is an unbalanced hierarchy. I think you can achieve quite a lot using a standard model with a master calendar (daily levels of granularity) and slowly changing dimensions. The problem is however how you count the number of employees - Min, Max or Average during a period?

If it is Average, then you should probably use Year/Quarter/Month as dimension and an expression along the following idea:

  Avg(Aggr(Count(distinct EmpID),Date))

HIC

0 Likes
335 Views
Not applicable

Hello Henric,

Do you know of any software to administrate such hierarchy (NodeId, ParentId, Name) and associate units (business units) to any level? Any recommendation.

For the moment, I developed my own Excel macro but it won't be viable in the long terms.

Thanks,

0 Likes
335 Views
sudeepkm
Valued Contributor III

Again Nice topic. informative. Thanks a lot.

0 Likes
335 Views
matthewjbryant
Contributor II

Did you get round to this blog post Henric Cronström?I have a complicated hierarchy of parts and component parts that I'm trying to model. The techniques discussed here look promising, but don't get me where I need to be, sadly.

Great post though.

0 Likes
335 Views
lornafnb
Valued Contributor

Hi Henric. Thanks for very informative post.

a question...I have an HR model...the table that Section Access is based on, is ideal for Hierarchy Load (table with adjacent nodes).

the actual data that I want to bring in - does that also have to be put thru Hierarchy statement to work - ie does it also have to be in same format (NodeID, Parent ID, Name)?

thanks

0 Likes
335 Views
Employee
Employee

Usually, you want the hierarchy resolution (the hierarchy prefix) in the data only, and a Section access that just lists nodes and who is allowed to see them (=no hierarchy prefix). See more on http://community.qlik.com/blogs/qlikviewdesignblog/2013/11/25/hierarchy-authorization

HIC

335 Views
lornafnb
Valued Contributor

I've done it just the other way around....

1. I read the Authorization file (this is an adjacent node table) with Hierarchy statement...

2. Then I get the Ancestor nodes...

3. Then I read the data file...and reduce to what I've found in (2).

Is this incorrect?

can I somehow send you the App so you can have a peep please?

this is my first experience with Section access and Hierarchy....

thanks

0 Likes
335 Views
Employee
Employee

If you test it and conclude that it works, then it works. There is not just one correct way to solve a problem.

It is not possible to attach a file in a blog comment. Open a discussion topic and attach it there.

HIC

0 Likes
335 Views
lornafnb
Valued Contributor

Thanks Henric

how can I tag you that you see the discussion, or do you see all my default.

I am using your blog to do the reduction and I would really appreciate your assistance since you wrote it.

0 Likes
335 Views
Employee
Employee

Just put my name in it, like Henric Cronström. (Type @ followed by henric and you will get a list of names.)

HIC

0 Likes
335 Views
bittecourt
Contributor II

Amigo Henric

Teria como postar como fazer uma tabela com grafico, com o calculando o percentual comparativo ano?

Adoro todo os seus POST, aprendo a cada dia com você e todos da community mais ainda tenho muito a aprender.

Agradeço .

0 Likes
335 Views
agni_gold
Valued Contributor

Can you please help me on my problem ..... please

https://community.qlik.com/thread/163345

0 Likes
335 Views
avastani
Contributor II

Try this

https://community.qlik.com/servlet/JiveServlet/download/5334-4-81955/Hierarchies.pdf

See if HierarchyBelongsTo helps you.

-afv.

Tel: 646.773.7936

0 Likes
335 Views