Load only records for the five last days - easy - or is it?

[datanibbler]

Hi,

in one app I am trying to load data from a qvd, only for the five last (numeric largest) dates:

- I have a date_value in that table

- I make that numeric with the num() function

- I use a WHERE() clause at the end of the LOAD statement and

- I include all the other fields in a GROUP BY clause

Is there anything else I need to take care of? I can't spot anything wrong - still, when I test it, the script aborts and I get the message "reload failed. Recover old data?"

Can someone help me there?

Thanks a lot!

Best regards,

DataNibbler

[agomes1971]

Hi,

Can you please post the .qvw and .qvd?

Regards

André Gomes

[sudeepkm]

I think you can create a variable to store the max date (from the QVD) in it

Then you can load records where DateField >= '$(VMxDate)'-5

[marcus_sommer]

Hi DataNibbler,

is there an aggregation-function, too? If yes, I assume there is a small syntax-issue - sometimes one is a little bit blind ... The simplest way to fix it is to comment some parts out and then ...

- Marcus

[agomes1971]

See if this is useful...

TempTable:

LOAD

     *

FROM File.QVD (qvd);

Data:

LOAD

     *

RESIDENT TempTable

ORDER BY Field1, Field2;

DROP TABLE TempTable;

Kind regards

André Gomes

[datanibbler]

Hi Marcus,

well, that is my aggregation function - should be the only one in the LOAD.

There must be something wrong based on the table_structure.

I have for now replaced that with the slightly longer way of concatenating five RESIDENT LOADs, one with max(5), one with max(4) and so on - now QV is multiplying the nr. of records by five - that means there must be some field that is absolutely unique in every record of the table - that is included in the GROUP BY clause, causing QV to sort of make a separate "data_heap" of every record and then loading it in every one of my five RESIDENT LOADs....

[maxgro]

//testdata:

//load

// floor(today() + rand()*1000) as d,

// floor(rand()*100) as exp

//autogenerate 10000;

//

//store testdata into testdata.qvd (qvd);

load * from testdata.qvd (qvd);

top5:          // last 5 date

first 5 load d;                    

load d, max(1) as m

resident testdata

group by d

order by d desc;              

inner join (testdata) load d Resident top5;

DROP Table top5;