Saving QVD's in periods.

[hopkinsc]

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'.

[hopkinsc]

Does anyone have any ideas on my problem please?

[hic]

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.

[hopkinsc]

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.

[#5]

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.