How to combine two rows of the same table and display it as old and new value in a single row
I'm bit of stumped in the syntax of qlikview to achieve this. I think I can work if it was an SQL statement but don't know how to do it in qlikivew. Here's my problem:
1. I have a table, say MainTable, which as a linear data:
MainTable:
Load * Inline
[
PartNum, Price, VendorNum, MM, YY
aa, 20, Vaa2, 3, 2013
aa, 30, Vaa3, 5, 2013
aa, 10, Vaa1, 1, 2013
bb, 10, Vbb1, 1, 2013
cc, 20, Vcc1, 1, 2013
bb, 15, Vbb2, 2, 2013
bb, 10, Vbb2, 5, 2013
cc, 15, Vcc2, 4, 2013];
What I want to achieve from this table is something like this (chronologically ordered price changes):
PartNum, OldPrice, NewPrice, OldVendorNum, NewVendorNum, OldMonth, NewMonth, OldYY, New YY
aa, 10, 20, Vaa1, Vaa2, 1, 3, 2013, 2013
aa, 20, 30, Vaa2, Vaa3, 3, 5, 2013, 2013
bb, 10, 15, Vbb1, Vbb2, 1, 2, 2013, 2013
bb, 15, 10, Vbb2, Vbb2, 2, 5, 2013, 2013
cc, 20, 15, Vcc1, Vcc2, 2, 5, 2013, 2013
I was thinking of doing something like this:
NeededTable:
NoConcatenate
Load
PartNum, Price as OldPrice, VendorNum as OLDVendorNum, MM as OLDMM, YY as OLDYY
Resident MainTable
Left Join (NeededTable)
Load Top 1
PartNum, Price as NewPrice, VendorNum as NewVendorNum, MM as NewMM, YY as NewYY
Resident MainTable
Where
OLDPrice<>Price AND OldMM<MM AND OLDYY<= YY
;
But I don't know how to do Top 1 in qlikview.
Please advise on how to achieve the resulting table. Thank you in advance.
Thank you. I did something similar to your post but with the exception that I did not use the Peek function. Thank you all for your help.
Yes, it does load the null value for the first one, but like you pointed out, it can be done by simply loading this table to another with a simple where condition. Thank you again.
