12 Replies Latest reply: May 11, 2015 6:21 AM by Andre Toerien RSS

    QVD export and load



      I need to do the following, would appreciate any ideas about the best way to do this.


      I have a Model which loads data per month. On the first run I need it to export data to qvd files per month example qvd201501.qvd, qvd201502.qvd etc.


      On the second run it should only run the current month, and export to qvd, with this exception. On the 1st of May for example it still needs to run the data for April, 2nd of May it should export for May only.


      Then in the actual model for the Users, I need to automatically import the last 12 month's qvds only

        • Re: QVD export and load
          Sergey Pokasov

          Hi, Andret!


          Would you try my expamle?





          let DTStart=MakeDate(2011,1,1);

          let DTEnd=Today(1);

          let YearStart=Year(DTStart);

          let YearEnd=Year(DTEnd);

          let MonthEnd=Month(DTEnd);


          for i=YearStart to YearEnd

            for j=1 to 12


              Set FileName=documents_$(i)_$(j).qvd;

              set TableName=documents;

              if (MakeDate(i,j,1)<=DTEnd and isnull(QvdCreateTime('$(QVDPath)\$(FileName)'))) or (i=YearEnd and j=MonthEnd) then


                sql select

                --top 1000

                doc_date "DateTime",

                dateadd(day, datediff(day, 0, doc_date), 0) "Date",


                doc_id "IDDocument",

                doc_num "NumDocument",

                dt_id "IDDocType",

                emp_id "IdEmployee"

                from documents

                where doc_date between dateadd(day,0,dateadd(month,$(j)-1,dateadd(year,$(i)-1900,0)))

                                   and dateadd(second,59,dateadd(minute,59,dateadd(hour,23,dateadd(day,-1,dateadd(month,$(j),dateadd(year,$(i)-1900,0)))))); 

                STORE $(TableName) into $(QVDPath)\$(FileName) (qvd); 

                Drop table $(TableName);

              end if;


            next j;

          next i;

            • Re: QVD export and load

              Thanks I will try, give me a few days, I will give feedback

              • Re: QVD export and load

                I am not making sense of this personally probably this is because I still quite new to this.


                I have a table DEBTORHISTORY with a field HPeriod., I need the setpath as well for the QVD's and then a way to split these field into files with one HPeriod per qvd


                This is the path name


                I would like to call the qvd's RE0001-"HPeriod"


                For the first load I want to do a complete load and create of qvd's, after that i want to let's say only create a new qvd for the current and previous month which will overwrite the old qvds

                  • Re: QVD export and load



                    I really like your idea, I just have a few questions. You set the dates etc fine, my question is regarding the timestamp field. I only have a YearMonth field.The in table in question we do not have a date added field as such, We have a process running which adds last months data on the first day of the new month to the source table from where we import the data, so there is not real datetime field. So for months prior to that the data is stored into qvd's on the first run, then every month on the first this model will basically create a new qvd file for the past month.


                    We will then use the qvd files to load it into another model where we have our dashboards.


                    Could you have a look at how I can change the script fot eh YearMonthField?

                • Re: QVD export and load
                  Peter Cammaert

                  Something like this?


                  // Make sure your source data is loaded as RESIDENT. Call this table SourceData with a field SD_Date

                  // Set QVDPath to the path of source/dest QVDs


                  FOR j = 0 to 11 // only cover 12 months

                    LET TableName = 'qvd$(=year(AddMonths(today(), $(j))))$(=num(Month(AddMonths(today(), $(j))), '00'))';

                    IF (j = 0) OR

                       ((j = 1) AND (Day(today()) = 1)) OR

                       IsNull(FileTime('$(QVDPath)\$(TableName).QVD')) THEN


                      LOAD * RESIDENT SourceData WHERE InMonth(SD_Date, today(), -$(j));

                      STORE $(TableName) INTO '$(QVDPath)\$(TableName).QVD';

                      DROP Table $(TableName);

                    END IF


                    • Re: QVD export and load

                      Trying to get my head around this, will have a look at this again tomorrow when i have a clear head, I am starting to pick up from the two eplies,  die logic is slowly starting to make sense

                        • Re: QVD export and load
                          Peter Cammaert

                          Feel free to ask any questions you may have. I admit that the code is a bit condensed at first sight

                            • Re: QVD export and load

                              I want to do an initial load, that this stage it is unsure how far back we will go, this depends from one clients to the next. In the initial load then i need to export all the files to qvd, including the current month, then also on the first of the month I still need to run the previous months as well. So going forward from the initial load I will only be pulling in the current month's data and this then needs to overwrite the olde file created the previous day...


                              As I understand it the first step like you said is setting the path, that is easy enough, after that you lose me a bit with the 12 months only, that is probably changeable. I will be getting the name of the file from Text for example RE0001 and then a hyphen with the Hperiod field which indicated=s the month for which the QVD is.


                              Not sure why I need to load it as a resident able, is this basically to create a temporary table with only the months data which is to be exported to qvd and then you drop the tav=ble after the export, then you do to the next months etc?


                              The code itself would be a bit easier if you can explain in words  what it does


                              FOR j = 0 to 11 // only cover 12 months--this can then probably be changed to how many months are required

                                LET TableName = 'qvd$(=year(AddMonths(today(), $(j))))$(=num(Month(AddMonths(today(), $(j))), '00'))';

                                IF (j = 0) OR

                                   ((j = 1) AND (Day(today()) = 1)) OR

                                   IsNull(FileTime('$(QVDPath)\$(TableName).QVD')) THEN

                                  $(TableName):--This bit from my previous comment I am unsure about, not sure anout the file time or why you specifically use j-0 or 1 with the first day of the month, obviously this is for the current month or if you are on the first day of the new month, I am just not sure exactly what the code is doing

                                  LOAD * RESIDENT SourceData WHERE InMonth(SD_Date, today(), -$(j));

                                  STORE $(TableName) INTO '$(QVDPath)\$(TableName).QVD';

                                  DROP Table $(TableName);

                                END IF


                              Hope I am making sense. I really appreciate your help

                                • Re: QVD export and load

                                  Hi Peter


                                  Can you please help here?

                                    • Re: QVD export and load
                                      Peter Cammaert

                                      Sorry, been busy lately. I'll do my best.


                                      12 months: yes you can change that. I didn't define a variable for the number of months you want to go bback because you stated that the final document will only load data from the last 12 months. Change the FOR loop start into something like:


                                      SET vNumOfMonthsToGoBack = 24; // Now we get a run for 24 (preceding) months


                                      FOR j = 0 TO $(vNumOfMonthsToGoBack) - 1


                                      File naming: the LET statement in the FOR body assigns a different name to each successive table. If I understand it correctly, you can modify the LET statement into something like. This. Note that if you want to cover more than 12 months, you should include the year as well. That's what this LET statement does.


                                      LET TableName = 'RE0001-$(=year(AddMonths(today(), $(j))))$(=num(Month(AddMonths(today(), $(j))), '00'))';


                                      Resident source data: just to make my example code simpler. The LOAD is from a central RESIDENT table that contains all data. YMMV. BTW Your assumption about how it operates is correct.


                                      Code comment 2: exactly, that's all there is to it. You create a QVD if one of these conditions is met:

                                      • For Current month (j = 0) =: always
                                      • For Previous month (j=1 and day = 1) = only on the first of the current month
                                      • For missing QVD = always. This is the big initial load.