5 Replies Latest reply: Apr 2, 2012 2:21 PM by obed.adames RSS

    Help - Multiple QVD Script

      Hi I want to create QVDs files by months and year

      For example I want to create depending on the year of thetransaction a qvd.

      In my data I have from 2006 to the present I want to create separateqvds files from the same load.

       

      Transaction_2009_Jan.QVD
      Transaction_2009_Feb.QVD
      Transaction_2009_Mar.QVD
      and so on ..

      Thanks

        • Help - Multiple QVD Script
          Jonathan Dienst

          Hi

           

          So what's the question?

           

          Regards

          Jonathan

            • Re: Help - Multiple QVD Script

              For example I have transaction

              From 2006 – 2012

              I want to read all and generate separate QVDs by month and year by a loop in the script.

              Don’t know how

                • Re: Help - Multiple QVD Script
                  Jonathan Dienst

                  Hi

                   

                  Something like this should do the trick (adjust field names as required):

                   

                   

                  
                  For zYear = 2006 To 2012
                       For zMonth = 1 To 12
                  
                           Let zMinDate = Date(MakeDate(zYear, zMonth, 1));
                           Let zMaxDate = Date(AddMonths(zMinDate, 1) - 1);
                           Let zFileName = 'Transaction_' & zYear & Month(zMinDate) & '.qvd';
                  
                           Transactions:
                           LOAD TransactionDate,
                                TransactionTime
                                ...
                            From ...
                            Where TransactionDate >= '$(zMinDate)' And TransactionDate <= '$(zMaxDate)';
                  
                  
                           STORE Transactions Into [$(zFileName)] (qvd);
                            DROP Table Transactions;
                  
                       Next
                  Next
                  
                  

                   

                  This assumes that the default date format in your QV model is the same as the format in your transaction source.

                   

                  Regards

                  Jonathan

              • Re: Help - Multiple QVD Script
                Hari Sury

                You may wish to try something along these lines:

                AllData:
                SQL SELECT * FROM <Source>;

                YearList:
                LOAD DISTINCT <Year-Field> as SaveYear
                RESIDENT AllData;

                LET vNumYears = NoofRows ('YearList');

                SET ii = 0;

                FOR ii = 0 to $(vNumYears) - 1

                LET vSaveYear = peek ('SaveYear', ii, 'YearList')

                Temp:
                LOAD *
                RESIDENT AllData
                WHERE <Year-Field> = $(vSaveYear);

                STORE Temp INTO Data-$(vSaveYear).qvd;

                DROP TABLE Temp;

                NEXT