Optimal way to load last 8 week data

[#1]

Hi,

I try to load only the last 8 week data, so I use below scripts:

where WEEK >= to_number(to_char(sysdate-56, 'iyyyiw')) and WEEK <= to_number(to_char(sysdate-7, 'iyyyiw'));

Then I store this data to a qvd.

However as the database is too big, I don't want to load 8 week data each week. I think it will be faster to delete the earliest week in the qvd, then append the new week into the qvd each time I reload. Is it possible to do that?

Peter

[tresesco]

You can try something like:

OldQVDData:

Load * from <Your_qvd> where WEEK >= ...; // for last 7 weeks

DataFromSource:

Concatenate  // it will get concatenated even automatically if all field are same

Load * from <Source_data> where WEEK >=....; // put here condition for latest week only

STORE OldQVDData Into <Your_qvd>;

Hope this helps.

[#3]

Thanks! That's a good idea.

I'm trying to load the last several weeks from a qvd, but I constantly get error message:

Directory;

LOAD week,

data1,

data2

FROM

A.qvd

(qvd)

where week>= to_number(to_char(sysdate-42, 'iyyyiw')) and week <= to_number(to_char(sysdate-14, 'iyyyiw'));

How do you make constraints when loading from qvd?

BR/Peter

[tresesco]

what is the error message? could be format issue.

[#5]

Error in expression:

TO_NUMBER is not a valid function

[Gysbert_Wassenaar · Accepted solution]

You're using oracle functions in your where clause. Qlikview doesn't understand oracle functions. Try using Qlikview functions instead:

where week >= num#(date(today()-42,'YYYYWW')) and week <= num#(date(today()-14,'YYYYWW'))

[qlikviewforum]

I am looking something similar to this. Can you please post the entire script?It will be helpful for me...