11 Replies Latest reply: Oct 29, 2014 8:33 AM by anbu cheliyan

# get max date from qvd

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

• ###### Re: get max date from qvd

Hi Jacob,

try like this:

Part,

Max(Date) as Date,

Sum(Price)

from QVD

group by Part;

Regards

KC

• ###### Re: get max date from qvd

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

• ###### Re: get max date from qvd

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

• ###### Re: get max date from qvd

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

• ###### Re: get max date from qvd

Hi,

Part,

Price,

Max(Date) as Date,

from QVD

group by Part;

• ###### Re: get max date from qvd

rawdata:

Part,

Max(DATE) as Date,

Sum(Price)

from QVD

group by Part;

• ###### Re: get max date from qvd

Part,

Firstsortedvalue(Date,-Date) as Date,

Firstsortedvalue(Price,-Date) as Price

from QVD

Group  by Part

• ###### Re: get max date from qvd

Hi anbu,

i think he need top value

i have small doubt

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

• ###### Re: get max date from qvd

Firstsortedvalue(Date,-Date) -- Due to minus on Date, you will get highest value

Check this ex

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

Part,Price,Date

a,10,01/01/2014

a,20,01/01/2015

b,10,01/01/2015 ];

• ###### Re: get max date from qvd

Brilliant

• ###### Re: get max date from qvd

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

Part,Price,Date

a,10,01/01/2014

a,20,01/01/2015

b,10,01/01/2015 ];