Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
datanibbler
Esteemed Contributor

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

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

6 Replies
agomes1971
Valued Contributor

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

Hi,

Can you please post the .qvw and .qvd?

Regards

André Gomes

sudeepkm
Valued Contributor III

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

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

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

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
Valued Contributor

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

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
Esteemed Contributor

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

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

MVP
MVP

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

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

Community Browser