Help - Multiple QVD Script

[#1]

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

[jonathandienst]

Hi

So what's the question?

Regards

Jonathan

[#3]

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

[jonathandienst · Accepted solution]

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

[malhaar]

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

[#6]

Thank you both for your help .. !!