topic Creating change hierarchy in App Development

Creating change hierarchy

MarkiMark — Wed, 23 Apr 2025 17:25:07 GMT

Hello,

I need your help creating hierarchy/history of material changes like in the example below via qlik script.
The aim is to list for each new version of the material all predecessors.

Original table:

OLD,NEW
68-25-01,68-25-02
68-25-02,68-25-03
68-25-03,68-25-04
15-15-04,15-15-05
74-12-01,74-12-02
38-90-06,40-00-01
40-00-01,40-00-02
40-00-02,40-10-01

Expected output:

OLD,NEW
68-25-01,68-25-02
68-25-01,68-25-03
68-25-01,68-25-04
68-25-02,68-25-03
68-25-02,68-25-04
68-25-03,68-25-04
15-15-04,15-15-05
74-12-01,74-12-02
38-90-06,40-00-01
38-90-06,40-00-02
38-90-06,40-10-01
40-00-01,40-00-02
40-00-01,40-10-01
40-00-02,40-10-01

Thank you for your help.

Re: Creating change hierarchy

Vegar — Thu, 24 Apr 2025 04:12:32 GMT

You could probably solve this by looking into and using the HierarchyBelongsTo functionality. It is designed for handling paren child node datasets like yours.

Re: Creating change hierarchy

MarkiMark — Thu, 24 Apr 2025 06:15:25 GMT

@Vegar thanks for the tip! I managed to get the desired result with the script below. I'm not using the HierarchyBelongsTo() function exactly as intended, but it gets the job done.

Data:

NoConcatenate Load * Inline [

OLD,NEW

68-25-01,68-25-02

68-25-02,68-25-03

68-25-03,68-25-04

15-15-04,15-15-05

74-12-01,74-12-02

38-90-06,40-00-01

40-00-01,40-00-02

40-00-02,40-10-01

];

HierarchyTable:

NoConcatenate

HierarchyBelongsTo (OLD, NEW, Replacement, AncestorID, AncestorName)

LOAD

Text(OLD) as OLD,

Text(NEW) as NEW,

'CVP/' & NEW as Replacement

Resident Data;

Drop Table Data;

NewVersion:

NoConcatenate

Load

OLD,

Text(SubField(AncestorName,'/',2)) as NEW,

Replace(AncestorName,'CVP/','CVP-') as Replacement

Resident HierarchyTable;

Drop Table HierarchyTable;