Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
Can you please post the .qvw and .qvd?
Regards
André Gomes
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
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
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
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....
//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;