Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Optimal way to load last 8 week data

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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'))


talk is cheap, supply exceeds demand

View solution in original post

6 Replies
tresesco
MVP
MVP

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.

Not applicable
Author

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

what is the error message? could be format issue.

Not applicable
Author

Error in expression:

TO_NUMBER is not a valid function

Gysbert_Wassenaar

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'))


talk is cheap, supply exceeds demand
qlikviewforum
Creator II
Creator II

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