Display two records fields in one row

[#1]

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

[sunny_talwar · Accepted solution]

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;

[#3]

Thx Sunny T,

that will do the trick. Thank you very much for your help 🙂

Igor