Discussion Board for collaboration related to QlikView App Development.
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.
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
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..
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
Brilliant, very elegant!
Many thanks.
M.
thank you,
you're welcome
regards
Marco