Rolling 6 month load from multiple QVD

[jeanjordaan007]

Hi.

I am going to discuss how i tackled a problem. After searching for a solution and not really finding one I hope my solution might help someone else or there might be someone with an even more efficient and simple way of doing things,

A bit of background. We have a some data sources that are rather big and we need to report on them for a set period. that period also tends to differ as the users needs changes or new department managers start. We create monthly QVD's as the data sources get used in multiple models. We also want to reduce the strain on the SQL servers. Os we create a QVD for the current month and archive it so we only load one months data at a time ( about 20mil lines per month) from multiple tables is SQL. The requirements also require us to update the model at least every hour.

The problem I have been sitting with is that I don't want to doe a

Load

    *

from folder\*

where date <= addmonths(date(today())

;

This will then load 4 years of QVD's ( 48 ) check the data and load where needed. I want the load script to only even open the QVD's with relevant data. The QVD's are saved with a YYYYMM affix. So we have data DATA201201 - DATA201603.

So looping  (201603 - 5) to 201603 wont give what we want. that will be 201598 to 201603 so not really what we want.

I finally came up with a solution I found to take the least amount of time to load and pretty easy to implement.

let vPeriod = 5; // This is the variable that gets changed as the users require to see more or less data. Depending on requirements. The idea is to Just change this one variable, save and reload and the data will adjust according.

for a = 0 to $(vPeriod) // loop for 6 iterations. This months and 5 preceding months.

LET vYearMonthtoLoad = Date(AddMonths(Today(),-$(a)),'YYYYMM'); // create the YYYYMM affix that will be used in the load

DATA:

LOAD

      Fielda,

      Fieldb,

      FieldC

FROM

..\MonthlyQVD\MonthlyEvents$(vYearMonthtoLoad).qvd(qvd);

next a;

Hope this helps someone and if there is another easier way of even a nice little QV function that I am not aware of, please add some comments. I would love to see if there is a even more efficient way of doing this.

[avinashelite]

you could try like

//calculation with days

where date(date_field,'DD-MM-YYYY')>= Date(today()-180,'DD-MM-YYYY') and date(date_field,'DD-MM-YYYY')<=Date(today(),'DD-MM-YYYY')

OR

where monthname(date_field)>= monthname(addmonths(monthname(today())-5) and monthname(date_field)<=monthname(today())

[jeanjordaan007]

The point is that I do not want to use a where clause.

That will load 48*20 mil lines and apply the where clause to check and load.

The way I applied it means that it will literally only load the QVD that needs to be loaded.

So for 5 months it will be (5*20Mil)

I used to have a where clause. But implementing this reduced my load time by 80% for the model using it.

We have about 10 Data loaders that load from various servers as well as about 40 models loading from all the sources on one server. So we need to have to minimize the resource load as much as possible .Especially with most models needing to refresh every hour.

[avinashelite]

Try like this

let vYearMonthtoLoad = Date(Monthname(Today())-$(a),'YYYYMM')

[jeanjordaan007]

That does not work.

Date(Monthname(Today())-1,'YYYYMM') = 201602

Date(Monthname(Today())-2,'YYYYMM') = 201602

Date(Monthname(Today())-30,'YYYYMM') = 201601

[avinashelite]

sorry try this

let vYearMonthtoLoad = Date( Addmonths ( Monthname(Today()) ,-$(a)),'YYYYMM')

[avinashelite]

Hi JeanJordaan007‌

Did the expression work??

[Gysbert_Wassenaar]

You could simplify the expression that calculates the value of the variable a bit:

LET vYearMonthtoLoad = Date(AddMonths(Today(),-$(a)),'YYYYMM');