topic Re: Find top node in hierachy in QlikView

Find top node in hierachy

martynlloyd — Fri, 05 Sep 2014 16:14:21 GMT

I have a parts supersession hierarchy: This is a m:1, n level, structure.

OldPart	NewPart
DF1439324	DF1617343
DF1617343	1617343
1610000	1617000
1617343	1797053
1617000	1797053
1797053	1878031
1878031	1800830

I want to build a table which maps any part to it's ultimate successor, we call this the Ultimate Part Number or UPN.

This would look like:

Part	UPN
DF1439324	1800830
DF1617343	1800830
1617343	1800830
1610000	1800830
1617343	1800830
1797053	1800830
1878031	1800830
1800830	1800830

I've tried Hierarchy and HierarchyBelongsTo, but I cannot achieve the result. The UPN must refer to itself as shown in the last row.

Many thanks for your help.

Re: Find top node in hierachy

JonnyPoole — Fri, 05 Sep 2014 16:42:13 GMT

Use this script to read the 2 field table as you've described:

Hierarchy ( NodeID, ParentID, NodeName, ,NodeName)

LOAD text(OldPart) as NodeID,

text(NewPart) as ParentID,

text(NewPart) as Parent,

text(OldPart) as NodeName

FROM

(ooxml, embedded labels, table is Sheet1);

Then you can create the chart you want and see the hierarchy too..

Re: Find top node in hierachy

MarcoWedel — Fri, 05 Sep 2014 22:16:08 GMT

Hi,

one possible solution:

tabParts:
LOAD * FROM [http://community.qlik.com/thread/132168] (html, codepage is 1252, embedded labels, table is @1);
LOAD NewPart, NewPart as OldPart Resident tabParts
Where not Exists(OldPart, NewPart);

tabPartHierarchy:
Hierarchy (OldPart, NewPart, Part)
LOAD *,
     OldPart as Part
Resident tabParts;

DROP Table tabParts;

hope this helps

regards

Marco

Re: Find top node in hierachy

martynlloyd — Mon, 08 Sep 2014 09:32:22 GMT

Brilliant, very elegant!
Many thanks.

Re: Find top node in hierachy

MarcoWedel — Mon, 08 Sep 2014 10:30:20 GMT

thank you,

you're welcome

regards

Marco