Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
martynlloyd
Partner
Partner

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

Brilliant, very elegant!
Many thanks.

M.

MarcoWedel

thank you,

you're welcome

regards

Marco