topic How Can I implement Hierarchy Function with my Data in QlikView

How Can I implement Hierarchy Function with my Data

victor5995 — Wed, 24 Dec 2014 16:40:10 GMT

Hello,

I have the data as follow and I am trying to create pivot table with Hierarchical structure.

To make Part A I need Component part of A1, A2, A4, A6 but again to make component part A2 I need A3, to make Component part A4 I need A5 and to make component part A6 I need part A7.

PARENT PART	LEVEL	PARENT OF THIS LEVEL	COMPONENT PART	Quantity
A	1	A	A1	1
A	1	A	A2	1
A	2	A2	A3	1
A	1	A	A4	1
A	2	A4	A5	1
A	1	A	A6	2
A	2	A6	A7	2

I want to get output in pivot when I collapse to the lower level something like as below.

PARENT PART	COMPONENT PART		Quantity
A	A1		1
	A2		1
		A3	1
	A4		1
		A5	2
	A6		2
		A7	2

Appreciate any help and Thanks in Advance.

Re: How Can I implement Hierarchy Function with my Data

Gysbert_Wassenaar — Wed, 24 Dec 2014 17:19:08 GMT

See this blog post: Unbalanced, n-level hierarchies. And this document: Hierarchies

Re: How Can I implement Hierarchy Function with my Data

victor5995 — Wed, 24 Dec 2014 19:25:15 GMT

Hi Gysbert,

I tried according to the documents provided in above link but can not figure out how to assign the values in Hierarchy syntax as per my data that I represent above.

Hierarchy (NodeID, ParentID, NodeName, [ParentName], [PathSource], [PathName], [PathDelimiter], [Depth])

Can you please help out with how to figure out as per my data which one is NodeID, ParentID,.... and all.

Thanks

Re: How Can I implement Hierarchy Function with my Data

cheburashka — Wed, 24 Dec 2014 21:39:58 GMT

Hello Victor,

Could you provide a csv with the data?

,KR Koen

Re: How Can I implement Hierarchy Function with my Data

Thu, 25 Dec 2014 09:51:55 GMT

Hi Victor,

Please check the below one.

Regards,

Sub2u

Re: How Can I implement Hierarchy Function with my Data

victor5995 — Thu, 25 Dec 2014 18:56:16 GMT

Hi Suba Redy,

How you achieved this on qlikview ?

Thanks

Re: How Can I implement Hierarchy Function with my Data

Thu, 25 Dec 2014 19:11:14 GMT

Hi,

Please find the below expression i used in pivot table..

1. Parent

2. =if (Parentofthislevel='A',[ComponentPart],[Parentofthislevel])

3. =if (Parentofthislevel='A2',[ComponentPart],If(Parentofthislevel='A4',[ComponentPart],If(Parentofthislevel='A6',ComponentPart) ) )

Wish U Happy X-Mas and Happy New Year in advance....

Sub2u.

Re: How Can I implement Hierarchy Function with my Data

MarcoWedel — Thu, 25 Dec 2014 20:12:14 GMT

Hi,

one solution could be:

tabIn:
LOAD * FROM [http://community.qlik.com/thread/146776] (html, codepage is 1252, embedded labels, table is @1);

Concatenate
LOAD [PARENT OF THIS LEVEL],
    [PARENT OF THIS LEVEL] as [COMPONENT PART]
Resident tabIn
Where not Exists ([COMPONENT PART], [PARENT OF THIS LEVEL]);

tabOut:
Hierarchy ([COMPONENT PART],[PARENT OF THIS LEVEL],[Component Part],,,Path,,Depth)
LOAD *,
    [COMPONENT PART] as [Component Part]
Resident tabIn;

DROP Table tabIn;

hope this helps

regards

Marco