A different approach to hierarchies with respect to the tree-view List Box

[#1]

I had been struggling for quite some time to find a hierarchy data model that works well within QlikView.

In particular, I want to be able to use the Tree-View in the List Box, and ensure that when a non-leaf node is selected, that the parent behave as we're used to showing distinctions between: children selected; all possible children selected; and all children selected.

If you generate your tree-view paths using the "hierarchy" load script command, you will find that selecting a parent on its own doesn't work very well, and selecting a parent (with children) results in only showing that some children have been selected, with the row coloured in grey and a little green dot at the far right.

This shortcoming is further exacerbated if you use the AJAX ZFC since all the list boxes must be the width of the longest path. QlikView will create a horizontal scroll bar, but it's pain to have to scroll both horizontally in order to see what has been selcted.

There is a solution to this problem though which I've yet to see posted in the QV Community Forum. The key to making the tree-view work properly in QlikView is by performing a "Reflexive Transitive Closure" operation on your hierarchy to produce "Closure" records. This goes one step beyond "flattening" that QV's hierarchy currently does. I'll provide a simple example below to show you what I mean.

Let's say I have a simple hierachy containing only two levels. And to keep it simple, there are only three rows: A parent and two children.

Here is the schema and data:

ParentId, MemberId, Name

[NULL], 1, "Mother"

1, 2, "Son"

1, 3, "Daughter"

If I use QlikView's hierarchy loader, I will get a new table that will look like this:

MemberId, Name1, Name2, Path

1, "Mother", [NULL], "Mother"

2, "Mother", "Son", "Mother/Son"

3, "Mother", "Daughter", "Mother/Daughter"

The problem is, in my fact table I don't have any rows which link directly back to MemberId 1.

But if I perform a Reflexive Transitive Closure, I instead get a table that looks like this:

MemberId, Name1, Name2, Path, Level

2, "Mother", "Son", "Mother", 1

2, "Mother", "Son", "Mother/Son", 2

3, "Mother", "Daughter", "Mother", 1

3, "Mother", "Daughter", "Mother/Daughter", 2

So in effect, I'm making a copy of every leaf node for each level I go up the hierarchy. This is why when I just click on "Mother", I am implicitly selecting MemberIds 2 and 3, which are both linked to my fact table (and all other dimensions).

As corollary I've noticed a significant reduction in my binary footprint (e.g. I went from 350 MB down to 300 MB) by changing all my hierarchies into Closures. Furthermore, I believe there may also be a performance improvement, although I haven't a proper benchmarking exercise. Furthermore, by adding a "Level" field, it is quite easy to determine the highest or lowest level selected. This too can come in handy.

As for performing a Reflexive Transitive Closure. Well, you'll probably have an easier time doing this using a different ETL tool (e.g. Pentaho DI includes a Closure step), or perhaps using Common Table Expressions in SQL. It would be nice to see an improved hierarchy function in QV itself.

[Anonymous]

Indeed - it is a very confusing behavior. I would prefer if selecting leafs on clicked level only would be done by different action (e.g. via right-click menu or maybe Alt-Click or even small button next to the + would be better).

This extra selection behavior should probably be optional (via properties) - as in most cases you dont have hierarchies with different depths, so by default it would work for most of the use cases and for rare occasion of mixed depth - people could turn this ok.

(Actually there are 3 possible behaviors - select exact level only, select direct children and select all descendants. Clicking Collapsed TreeView selects all the descendants).

EDIT: But this can actually be worked around by applying nhepburn proposal - and link via leaf id (not via path).

[Anonymous]

You can fix the extra records for non-leaf levels easily:

First load ancestors table via HIERARCHYBELONGSTO(NodeId, ParentId, NodeName, AncestorId, AncestorName, DepthDiff)

and then create final table by LOAD Resident filtering out extra records - i.e. WHERE DepthDiff = 0 (or you can use WHERE AncestorId = NodeId - you can choose whichever fields are sufficient).

[Anonymous]

Here i will add one small clarification (which was confusing me on initial read).

Fields Name1 and Name2 are actually not needed in the closure - it is completely satisfactory to just link Path and MemberId (and make sure you use MemberId in your transaction data table - not the Path).

And btw - as we are only associate leafs with ancestors (but not ancestors with themselves), isnt that only transitive closure (i.e. no reflexive part)?