Creating change hierarchy

[MarkiMark]

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.

[Vegar · Accepted solution]

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

[MarkiMark · Accepted solution]

@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;