Find top node in hierachy

[martynlloyd]

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.

M.

[JonnyPoole]

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..

[MarcoWedel · Accepted solution]

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

[martynlloyd]

Brilliant, very elegant!
Many thanks.

M.

[MarcoWedel]

thank you,

you're welcome

regards

Marco