How to Reload some Part of code once in a month?

[brucemazynzolt]

Hi Experts,

Below is the code trying to refresh, Table1 based on monthwise, Here If condition is satisfied then my code is working fine if not monthwise data is completely skipping like below.

Date.              Sales
11-01-2018.       210
11-02-2018.       400
11-03-2018.        320
.
.
what I am expecting here is 'if month wise data IF condition is not satisfied then skip to next step, but already existing data till last month(Month wise) should display in report like below. Just to avoid daily loading of Table1(Month wise data)

Date.              Sales
Aug-2018       10000
Sep-2018        20000
Oct-2018        30000
11-01-2018.       210
11-02-2018.       400
11-03-2018.        320
.
.

Below is the sample code:

LET vEveryMonth = Num(MonthStart(today()));

If $(vEveryMonth) = Num(Today()) then

//Need to refresh once in a Month(start date of every month)
//Loading Month wise data
Table1:
Load * From Facttable.qvd(QVD)
WHERE DATE(floor(CalendarDate),'YYYYMMDD')<=date(floor(monthend(addmonths(Today(),-1))), 'YYYYMMDD');

ENDIF;

concatenate

//Loading Daily Data
Table2:
Load * From Facttable.qvd(QVD)
Where DATE(floor(CalendarDate),'YYYYMMDD')>date(floor(monthend(Today(),-1)), 'YYYYMMDD

Here my intention using the same qvd from both the tables 1.monthly summarising data from 1st table shows monthwise, if the month is current month it should show only daily data from 2nd table.
Concatenating both qvd’s will get result like below sample.

Date.              Sales
Aug-2018       10000
Sep-2018        20000
Oct-2018        30000
11-01-2018.       210
11-02-2018.       400
11-03-2018.        320
.
.
Like wise until the current month gets completed should show in date wise .

I have achieved the report as per the requirement but only one thing here is I need to avoid the daily reload of 1st table means Month qvd.

As my IF condition is working only date gets matched else completely skipping the month qvd and not showing month data in report so looking for the logic to get load once in a month of month qvd.

[stevedark]

You need to load all data from QVDs into the front end everytime. 

If the QVD load is optimised then this should not be too big an overhead. 

It's the refreshing of the monthly QVD that you will want to avoid doing too often. 

[brucemazynzolt]

Hi Steve,

Thanks for your reply and sorry for the late response.
Here I am trying to load only 1 year data but when it comes to load monthly qvd it is taking almost 45 mins, and the total application takes 70 mins, so when we avoid daily reload of monthly qvd then the app might takes not more than 30 mins.
so please suggest any other way is possible.

[stevedark]

It is likely that the load of the monthly QVD is not optimised.

Ensure that any manipulation of the data is done when creating the QVD, not when loading from it. Ensure that the only WHERE statement is a single WHERE EXISTS on a field that is included in the load. Also ensure that if you are concatenating data that the table structure of the table you are concatenating to is identical to the one being concatenated.

If you post your load script I can take a look and see if I can spot where it is not optimised.

How big is the QVD that is being loaded?

Steve