Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi
So what's the question?
Regards
Jonathan
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
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
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
Thank you both for your help .. !!