Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
See this blog post: Unbalanced, n-level hierarchies. And this document: Hierarchies
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
Hello Victor,
Could you provide a csv with the data?
,KR Koen
Hi Victor,
Please check the below one.
Regards,
Sub2u
Hi Suba Redy,
How you achieved this on qlikview ?
Thanks
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.
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