topic Re: find the 100 oldest dates in QlikView

find the 100 oldest dates

Wed, 24 Apr 2013 11:10:14 GMT

I got a qvd file with amongst others a field with dates(dd-mm-yyyy).

I want to find the 100 oldest dates - how do I solve that?

Re: find the 100 oldest dates

spividori — Wed, 24 Apr 2013 13:09:44 GMT

Hi.

See the example.

Hope this help!

Regards.

Re: find the 100 oldest dates

Gysbert_Wassenaar — Wed, 24 Apr 2013 14:11:40 GMT

Depends on what you want to do. If you want to keep only the oldest 100 from the qvd you can first load them and then load the first 100 ordered descending by date:

Temp:

load * from myqvd.qvd (qvd);

Result:

first 100

load * resident Temp order by mydate desc;

If you want to keep all the records you could instead add a flag:

Result:

load *, if(rowno()<=100,1,0) as fOldest100

resident Temp order by mydate desc;

If you want to find the oldest 100 in a chart you can try using the rank() function. Or if you're using QV11 perhaps Dimension Limits. Or something like if(mydate<=min(mydate,100),mydate)

Re: find the 100 oldest dates

Mon, 29 Apr 2013 11:38:53 GMT

Hi Gysbert

I tried

load *, if(rowno()<=100,1,0) as fOldest100

resident Temp order by mydate desc;

but get an error "Table not found"

Probably just me - I changed mydate to myfieldname and ran the load - but same result

is there something with the rowno???