Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Display two records fields in one row

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

1 Solution

Accepted Solutions
sunny_talwar

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;


Capture.PNG

View solution in original post

2 Replies
sunny_talwar

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;


Capture.PNG

Not applicable
Author

Thx Sunny T,

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

Igor