How to unpile a table with QlikView

[apoggio2011]

Hello,

I have an history table with relation of two fields A and B which builds the chain of history.

Example:

NUMBER          A          B

3501               1000     1944

3501               1944     3433

3542               3433     3469

3542               3469          -

The chain is from field B --> A 1000 --> 1944 --> 3433 --> 3469 (last)

I want to be able to say the NUMBER 3501 last value is 3542

Thx for your help

[Anil_Babu_Samineni]

How you want to present in result? Can you tell us script/Front end?

[apoggio2011]

Hello, thank you for your reply.

The final result is a table showing the following:

NUMBER     NEW_NUMBER

3501               3542

...                    ....

...                    ....

An intermediate table, but not mendatory would be

NUMBER     B          A_1          A_2          A_3          A_n          NEW_NUMBER

3501           1944      3433          3469        null          null              3452

Thx

[apoggio2011]

What I don't know how to do is  a loop on the script to seek for each line multiple time on all table the chain between filed B and A until it does not exist anymore.

I've been able to create 5 left outer join on the main table with every time the new value and then concatenate all table to achieve this task.

But in the future I may have more then 5 steps to find the last corresponding NUMBER!

[MarcoWedel · Accepted solution]

Did you already try a hierarchy load?:

https://help.qlik.com/en-US/qlikview/November2018/Subsystems/Client/Content/QV_QlikView/Scripting/Sc...

hope this helps

regards

Marco

[apoggio2011]

Great. THANKS.

[MarcoWedel]

You're welcome
Glad it helped
Marco