7 Replies Latest reply: Mar 24, 2016 1:20 AM by Jean Jordaan RSS

    Rolling 6 month load from multiple QVD

    Jean Jordaan

      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.