topic Re: Child count in hierarchy in QlikView

Child count in hierarchy

Wed, 28 Aug 2013 08:40:10 GMT

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 ?

Re: Child count in hierarchy

hic — Wed, 28 Aug 2013 08:53:00 GMT

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

Re: Child count in hierarchy

Wed, 28 Aug 2013 09:41:27 GMT

yes I can send an xls or txt file with the data and the load script !

Re: Child count in hierarchy

Wed, 28 Aug 2013 10:01:59 GMT

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

;

Re: Re: Child count in hierarchy

hic — Wed, 28 Aug 2013 10:56:13 GMT

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

Re: Child count in hierarchy

Wed, 28 Aug 2013 13:42:32 GMT

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

Re: Child count in hierarchy

hic — Wed, 28 Aug 2013 14:23:35 GMT

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