topic Re: Hierarchy: count sub-levels in QlikView

Hierarchy: count sub-levels

Fri, 30 May 2014 10:55:04 GMT

Hi,

I have two tables: the first one is call CATEGORY and essentially represents a 5 levels tree of categories; the second one is called REQUEST_DETAIL and is a list of records each one categorized using the categories defined in the CATEGORY table.

I loaded the CATEGORY table in qlikview using the HIERARCHY function, so I have the structure of the categories split in different levels and the depth of each category.

When I select depth = 1 (all the roots of the tree) I would like to count in the second table not only the number of records corresponding to the selected categories but even the number of records correspoding to the sons of the selected roots.

Same thing if i select depth = 2 (in this case all roots will be excluded) and so on.

It's 2 days that i'm trying to find a solution to this problem, can anyone help me?

Here' s an extract of the tables:

CATEGORY

CATEGORY_ID	CATEGORY_NAME	FATHER_ID
1	A
2	B	1
3	C	1
4	D	3
5	E	4
6	F	4

DEPTH TREE STRUCTURE

1 A

2 B C

3 D

4 F E

REQUEST_DETAIL

ID	CATEGORY_ID
1	1
2	2
3	3
4	2
5	5
6	6
7	5
8	5

So, counting the number of records with the same category i have:

A = 1 time

B = 2

C = 1

D = 0

E = 3

F = 1

If I select DEPTH = 1 I shoud see:

A = 1+2+1+0+3+1

If I select DEPTH = 2:

B = 2 and C = 1+0+3+1

DEPTH = 3:

D = 0+3+1

DEPTH = 4:

E = 3 and F = 1

Thanks in advance.

Re: Hierarchy: count sub-levels

Tue, 03 Jun 2014 07:01:15 GMT

Any ideas?

Re: Hierarchy: count sub-levels

Tue, 03 Jun 2014 08:59:35 GMT

Hi,

you need to load your category with HierarchyBelongsTo().

regards

Darek

Re: Hierarchy: count sub-levels

Tue, 03 Jun 2014 10:29:40 GMT

Andreo,

let me know if you need more help ....

regards

Re: Hierarchy: count sub-levels

Tue, 03 Jun 2014 13:52:05 GMT

Hi Darek,

thanks to your indication, i stumbled upon this video:

http://www.youtube.com/watch?v=ywz8WNitL1k

that clarified me the difference between Hierarchy and HierarchyBelongsTo.

I solved the problem loading with Hierarchy and creating a pivot table that allows to expands from level 1 to level 5.

Is it possible to graph what i see in the pivot table? (if I simply switch from pivot to linear chart the graph counts only the Leaves correspondig to the selected level)

Thanks,

Andreos

Re: Hierarchy: count sub-levels

Thu, 15 Dec 2016 00:23:56 GMT

Hi Andreos. I have a similar problem. Could you share your QVW with the solution, if you still have it?

Thanks,

Luciano

Re: Hierarchy: count sub-levels

Thu, 15 Dec 2016 09:55:56 GMT

HI Luciano,

I'm sorry I can't share the document since it contains reserved information of my company.

Anyway I can confirm what I did, I create a pivot table with the levels as dimensions and the data you want to count as expression and I left the levels expandable. If you expand level 1 you see the count of level 2,3,4 and 5 and so on.

I hope this can help you.

Regards.

Re: Hierarchy: count sub-levels

Thu, 15 Dec 2016 11:40:40 GMT

Thanks for reply.

I watched the video you suggested and I'll try the same what you've done.

Regards.

2016-12-15 7:56 GMT-02:00 Nicola Sardone <qcwebmaster@qlikview.com>: