4 Replies Latest reply: Jan 14, 2012 4:55 PM by David Braune RSS

    Saving QVD's in periods.

    Chris Hopkins

      Hi, we have a script that runs every night which creates our QVD's. We have 21 QVD's in total and a total of around 300mb is added to them daily, but now we are having problems as they are now so big that it takes 8.5 hrs to complete. It is the saving that takes the time as a few of the QVD's are now over 20GB each.

      We want to try and seperate them into periods but am unsure how.

      What i want is if for example we are currently in period 9, then all files that are processed daily that are from period 9 get saved into a period 9 QVD (POSTransactionDetail-2011-Period9.QVD)

       

      One other thing to consider is that if a file comes in late (say it comes into us in period 9 but the actual file is a period 8 file) then this will need to be added to the Period 8 QVD.

      We have a QVD that contains the Period numbers and dates, CapperCalender.QVD, field [Cal_Year-Period].

      Here is a sample of the script we are currently using.

       

      IF UPPER ('$(vBasename)') = UPPER ('POSTransactionDetail') THEN

         filelist:

         LOAD '$(vBasename)' as basename

         AUTOGENERATE 1

         ;

         [$(vBasename)]:

         LOAD    @1 AS StoreNumber,

           @2 AS TransId,

           @3 AS LineNo,

           @4 AS EANCode,

           @5 AS CommodityGroup,

           @6 AS LineType,

           @7 AS SaleUnit,

           @8 AS ItemPrice,

           @9 AS SalesValue,

           @10 AS VATAmount,

           @11 AS SalesQuantity,

           @1&'-'&@2 AS %LkStorePosTrans, // Join of Postransactionheader & postransactiondetail

           @1&'-'&@2&'-'&@3 AS %LkStoreTranLineAge // Join postransactiondetail & agechecksale

       

         FROM $(file) (ansi, txt, delimiter is ',', no labels, msq)  ;

       

      SET cFileName =$(RetailDaily)TMPpostransactiondetail.QVD;

      IF QvdCreateTime('$(cFileName)') >= 0 THEN

      POSTransactionDetail:

      LOAD DISTINCT *

      FROM $(cFileName) (qvd);

       

      // Check to see if a past record already exists

      // If a past record exists with the same UNIQUE_ID, dont load

      // always take the last loaded

      SET cFileName =$(vcRetailQVD)POSTransactionDetail.QVD;

      IF QvdCreateTime('$(cFileName)') >= 0 THEN

        POSTransactionDetail:

        CONCATENATE LOAD DISTINCT *

        FROM $(cFileName) (qvd);

      ENDIF

       

      IF NoOfRows('POSTransactionDetail') > 0 THEN

        STORE * FROM POSTransactionDetail INTO $(vcRetailQVD)POSTransactionDetail.QVD;

        DROP TABLE POSTransactionDetail;

      ENDIF 

      ENDIF

       

       

      Does anyone have any ideas?

       

      Chris

       

      I wasnt sure where to post this, so i have also posted it in 'New to Qlikview'.

        • Saving QVD's in periods.
          Chris Hopkins

          Does anyone have any ideas on my problem please?

          • Saving QVD's in periods.
            Henric Cronström

             

            Would including the period number in the file name be a solution? Then you could have one qvd file per month.

             

            The creation could look like:

             

            // --- define period
            Let vYear = Year(Today()) ;
            Let vPeriod = Num(Month(Today()), '00') ;

            // --- load the records pertaining to this period
            DataThisMonth:
            Load *
            From $(file) (ansi, txt, delimiter is ',', no labels, msq)
            Where Year(TransactionDate)=$(vYear) and Month(TransactionDate)=$(vPeriod);

            // --- create qvd
            Store DataThisMonth into DataThisMonth$(vYear)$(vPeriod).qvd (qvd);

             

             

             

            And the loading of the qvd files could look like:

             

            // --- loop over all periods
            for vYear = 2008 to 2012
            for vPeriod = 1 to 12
            Let vPeriod = Num(vPeriod, '00');
            Load * From DataThisMonth$(vYear)$(vPeriod).qvd (qvd);
            next vPeriod
            next vYear

             

            This way you would smaller files and never get the same record in two files.

             

              • Saving QVD's in periods.
                Chris Hopkins

                Hi Henric,

                Thanks for your reply.

                 

                I already have my variable for the year and period which is taken from our calander QVD:

                 

                LET vYearPeriod = peek('Cal_Year-Period',-1,CapperCalender);

                 

                I think the above is correct ??

                 

                Then here is the part that stores the data into the QVD:

                 

                IF NoOfRows('ProductSale') > 0 THEN

                  STORE * FROM ProductSale INTO $(vcRetailQVD)ProductSale_$(vYearPeriod).QVD ;

                  DROP TABLE ProductSale;

                 

                This then adds the year and period no. onto the end of the filename and will add any future files that are processed in the same period.

                 

                This works for us at the moment but is not ideal as if a file is processed late (eg. a file from the last period gets processed in the current period) then this file will be saved in the current period QVD whereas it should be saved in the last period QVD.

                 

                So what i am looking for now is to have something in the script which will comapre the transaction dates within the file and save those transactions in the relevant QVD.

                 

                Do you have any ideas how i can do this please?

                 

                Thanks for your help.

              • Saving QVD's in periods.
                David Braune

                I would suggest that you add code to read the min and max dates in any file you are loading and then make the decision whether to load the new data into an existing file or create a new one.

                 

                The script is trivial

                 

                tmpMinMax:

                LOAD

                     min(Date) AS minDate,

                     max(Date) AS maxDate

                FROM

                      yourdatasource;

                 

                Let vminDate = peek(....

                 

                From here you can DROP what you don't need and then go on to figure which file (OLD or new) the data should be added to.