Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello
Yesterday I've discovered the hierarchy function in Qlikview
This is great !
So I set the result in a listbox displayed as TreeView
But Is it possible to display the child count fo each node ?
Yes and No...
You can achieve it fairly simply if you build a smart data model, but there is no built-in capability to do this - or rather, it is most likely possible if you use a very complex formula....
Could you post your data, so I can take a look at it? I am working on a blog post on this topic, and would appreciate some examples.
HIC / QlikTech Technical Product Advocate
yes I can send an xls or txt file with the data and the load script !
Here is the file
http://www.filedropper.com/hierarchytest
Here is the script for sql load but easy to adapt for the textfile
ccy:
Hierarchy(hcompanyid,hparentid,hcompanyname,brolekes,hcompanyname)
load
companyid as hcompanyid,
parentid as hparentid,
companyname as hcompanyname,
parentname as hparentname;
SQL SELECT DISTINCT
company.companyid,
company.parentcompanyid as parentid,
parent.name AS parentname,
company.name AS companyname
FROM
public.fs_company company
INNER JOIN public.fs_company parent on parent.companyid=company.parentcompanyid
WHERE
company.active='t'
and company.companyid<>1
;
Olivier
I would load this using the attached script.
1: Your file is missing one record: The root node. Note that every node must have a record of its own. I fix this by loading all nodes int two steps into a temporary table using "Where Not Exists()".
2: I use HierarchyBelongsTo to generate all subnodes of a node. Then the number of subnodes can be calculated using Count(distinct SubNodeID).
HIC
Thank you Henric
Ok I understand the tip to get the root parent
Anyway it just give a root level in the treeview : the ttreeview was working without it !
Now I'm trying to understand the concept of HierarchyBelongsTo which is not very clear !
I'm developping in Qlikview since a few day ans some mechanism are a bit confusing
Probably because I get my habit from years of C and cSharp programing
HierarchyBelongsTo is quite simple. It returns a list of all Ancestor-Daughter combinations. If your input is
NodeID | ParentID | NodeName |
3 | 2 | France |
4 | 3 | Bordeaux |
5 | 4 | Medoc |
it will return
NodeID | NodeName | AncestorID | AncestorName |
3 | France | 3 | France |
4 | Bordeaux | 4 | Bordeaux |
5 | Medoc | 5 | Medoc |
4 | Bordeaux | 3 | France |
5 | Medoc | 4 | Bordeaux |
5 | Medoc | 3 | France |
HIC