Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Can you help me with this?
I have table with two records for one article (old price and newprice)
I want do display all in one row..
Example No, Description, new price, old price..Sample of data is below
LOAD * INLINE [
No, Descripton, FromDAte, "[Unit Price]"
000876, ARICEPT TBL 28 X 5 MG PFI, 30.9.2016, "11,71"
000876, ARICEPT TBL 28 X 5 MG PFI, 31.3.2016, "11,80"
];
Thanks in advance, Igor
May be like this:
Table:
LOAD * INLINE [
No, Descripton, FromDAte, Unit Price
000876, ARICEPT TBL 28 X 5 MG PFI, 30.9.2016, "11.71"
000876, ARICEPT TBL 28 X 5 MG PFI, 31.3.2016, "11.80"
];
FinalTable:
LOAD No,
Descripton,
Date(Max(FromDAte)) as FromDAte,
FirstSortedValue([Unit Price], -FromDAte) as NewPrice
Resident Table
Group By No, Descripton;
Join (FinalTable)
LOAD No,
Descripton,
FirstSortedValue([Unit Price], -FromDAte, 2) as OldPrice
Resident Table
Group By No, Descripton;
DROP Table Table;
May be like this:
Table:
LOAD * INLINE [
No, Descripton, FromDAte, Unit Price
000876, ARICEPT TBL 28 X 5 MG PFI, 30.9.2016, "11.71"
000876, ARICEPT TBL 28 X 5 MG PFI, 31.3.2016, "11.80"
];
FinalTable:
LOAD No,
Descripton,
Date(Max(FromDAte)) as FromDAte,
FirstSortedValue([Unit Price], -FromDAte) as NewPrice
Resident Table
Group By No, Descripton;
Join (FinalTable)
LOAD No,
Descripton,
FirstSortedValue([Unit Price], -FromDAte, 2) as OldPrice
Resident Table
Group By No, Descripton;
DROP Table Table;
Thx Sunny T,
that will do the trick. Thank you very much for your help 🙂
Igor