topic Re: get max date from qvd in QlikView

get max date from qvd

Wed, 29 Oct 2014 12:03:23 GMT

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

jyothish8807 — Wed, 29 Oct 2014 12:10:41 GMT

Hi Jacob,

try like this:

Load

Part,

Max(Date) as Date,

Sum(Price)

from QVD

group by Part;

Regards

Re: get max date from qvd

Wed, 29 Oct 2014 12:15:38 GMT

Hi,

Load

Part,

Price,

Max(Date) as Date,

from QVD

group by Part;

Re: get max date from qvd

Wed, 29 Oct 2014 12:19:50 GMT

rawdata:

Load

Part,

Max(DATE) as Date,

Sum(Price)

from QVD

group by Part;

Re: get max date from qvd

Wed, 29 Oct 2014 12:19:55 GMT

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

Re: get max date from qvd

Wed, 29 Oct 2014 12:21:30 GMT

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

Re: get max date from qvd

jyothish8807 — Wed, 29 Oct 2014 12:22:24 GMT

Hi Jacob,

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

Regards

Re: get max date from qvd

anbu1984 — Wed, 29 Oct 2014 12:23:25 GMT

Load

Part,

Firstsortedvalue(Date,-Date) as Date,

Firstsortedvalue(Price,-Date) as Price

from QVD

Group by Part

Re: get max date from qvd

anbu1984 — Wed, 29 Oct 2014 12:25:04 GMT

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 ];

Re: get max date from qvd

Wed, 29 Oct 2014 12:25:29 GMT

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

Wed, 29 Oct 2014 12:25:58 GMT

Brilliant

Re: get max date from qvd

anbu1984 — Wed, 29 Oct 2014 12:33:44 GMT

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;

Load * Inline [

Part,Price,Date

a,10,01/01/2014

a,20,01/01/2015

b,10,01/01/2015 ];