Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
martynlloyd
Partner - Creator III
Partner - Creator III

Find top node in hierachy

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

OldPartNewPart
DF1439324DF1617343
DF16173431617343
16100001617000
16173431797053
16170001797053
17970531878031
18780311800830

com.JPG.jpg

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:

PartUPN
DF14393241800830
DF16173431800830
16173431800830
16100001800830
16173431800830
17970531800830
18780311800830
18008301800830

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.

1 Solution

Accepted Solutions
MarcoWedel

Hi,

one possible solution:

QlikCommunity_Thread_132168_Pic1.JPG.jpg

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

View solution in original post

4 Replies
JonnyPoole
Employee
Employee

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

hierarchy.PNG.png

MarcoWedel

Hi,

one possible solution:

QlikCommunity_Thread_132168_Pic1.JPG.jpg

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
Partner - Creator III
Partner - Creator III
Author

Brilliant, very elegant!
Many thanks.

M.

MarcoWedel

thank you,

you're welcome

regards

Marco