topic Re: Optimal way to load last 8 week data in QlikView

Optimal way to load last 8 week data

Thu, 18 Jul 2013 02:54:34 GMT

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

Re: Optimal way to load last 8 week data

tresB — Thu, 18 Jul 2013 04:33:24 GMT

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.

Re: Optimal way to load last 8 week data

Thu, 25 Jul 2013 08:19:28 GMT

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

Re: Optimal way to load last 8 week data

tresB — Thu, 25 Jul 2013 08:37:53 GMT

what is the error message? could be format issue.

Re: Optimal way to load last 8 week data

Thu, 25 Jul 2013 08:59:18 GMT

Error in expression:

TO_NUMBER is not a valid function

Re: Optimal way to load last 8 week data

Gysbert_Wassenaar — Thu, 25 Jul 2013 08:59:34 GMT

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

Re: Optimal way to load last 8 week data

qlikviewforum — Thu, 31 Oct 2013 07:32:31 GMT

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