get max date from qvd

[#1]

Hi,

I want to pull from  QVD only the max date of each product.

How can I do that? see example below.

Raw Data:

PART  PRICE    DATE

a          10          01/01/2014

a          20          01/01/2015

b          10          01/01/2015

Output:

a          20          01/01/2015

b          10          01/01/2015

Thanks to all,

Jacob

[jyothish8807]

Hi Jacob,

try like this:

Load

Part,

Max(Date) as Date,

Sum(Price)

from QVD

group by Part;

Regards

KC

[#3]

Hi,

Load

         Part,

         Price,

         Max(Date) as Date,

from QVD

group by Part;

[#4]

rawdata:

Load

Part,

Max(DATE) as Date,

Sum(Price)

from QVD

group by Part;

[#5]

Thanks for the advice

But I think you got it wrong

I don't want sum of price,I want to get only the row of the last date

[#6]

then put price instead of sum(price) and make sure  field name should be same

[jyothish8807]

Hi Jacob,

Sum(Price) will give you sum for  the customer only. Did you try it? The same row will come as output.

Regards

KC

[anbu1984 · Accepted solution]

Load

Part,

Firstsortedvalue(Date,-Date) as Date,

Firstsortedvalue(Price,-Date) as Price

from QVD

Group  by Part

[anbu1984]

Load Part,Firstsortedvalue(Date,-Date) as Date,Firstsortedvalue(Price,-Date) as Price Group by Part;

Load * Inline [

Part,Price,Date

a,10,01/01/2014

a,20,01/01/2015

b,10,01/01/2015 ];

[#10]

Hi anbu,

i think he need top value

i have small doubt

Firstsortedvalue(Date,-Date) as Date   it gives top or lowest value  ?