Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Child count in hierarchy


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 ?

Tags (2)
6 Replies
Employee
Employee

Re: Child count in hierarchy

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

Not applicable

Re: Child count in hierarchy

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

Not applicable

Re: Child count in hierarchy

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

;

Employee
Employee

Re: Re: Child count in hierarchy

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

data model.png

Not applicable

Re: Child count in hierarchy

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

Employee
Employee

Re: Child count in hierarchy

HierarchyBelongsTo is quite simple. It returns a list of all Ancestor-Daughter combinations. If your input is

NodeIDParentIDNodeName
32France
43Bordeaux
54Medoc

it will return

NodeIDNodeNameAncestorIDAncestorName
3France3France
4Bordeaux4Bordeaux
5Medoc5Medoc
4Bordeaux3France
5Medoc4Bordeaux
5Medoc3France

HIC

Community Browser