Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Valued Contributor II

Saving QVD's in periods.

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


   LOAD '$(vBasename)' as basename




   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



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



  FROM $(cFileName) (qvd);


IF NoOfRows('POSTransactionDetail') > 0 THEN

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

  DROP TABLE POSTransactionDetail;




Does anyone have any ideas?


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

4 Replies
Valued Contributor II

Saving QVD's in periods.

Does anyone have any ideas on my problem please?


Saving QVD's in periods.

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

Valued Contributor II

Saving QVD's in periods.

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.

Not applicable

Saving QVD's in periods.

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



     min(Date) AS minDate,

     max(Date) AS maxDate



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.

Community Browser