find the 100 oldest dates

[#1]

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?

[spividori]

Hi.

See the example.

Hope this help!

Regards.

[Gysbert_Wassenaar]

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)

[#4]

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???