Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
I have a problem with the below scenario, can any one help me on this.
Problem:
I have generated 5 years of data in qv, now I want to generate separate qvds for each month to every year, I have tried with below code , it is generating for every month even for the current year but I need qvds for up to September for current year as I didn't have data for the coming months , and also when I reload the application daily the application will update only the current month qvd with new data generated daily. How to do this ?
Code:
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='hh:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='MM/DD/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
LET vmindate= num(AddYears(num(Today()),-3));
Date:
LOAD
Rand()+24 as Sales,
Date(Date,'MM/DD/YYYY') AS Date;
LOAD
$(vmindate)+RecNo()-1 as Date
AutoGenerate num(Today())-$(vmindate);
Year:
LOAD
Distinct
Year(Date) As Year
Resident Date
Order By Date;
LET vYears= NoOfRows('Year');
Month:
LOAD
Distinct
Month(Date) As Month,
num(Month(Date)) As MonthNo
Resident Date
Order By Date ;
LET vMonths= NoOfRows('Month');
for i=0 to $(vYears)-1
LET vyear= Peek('Year',$(i),'Year');
FOR j=0 to $(vMonths)-1
LET vMonthName= Peek('Month',$(j),'Month');
LET vMonthNo= Peek('MonthNo',$(j),'Month');
Sales:
LOAD
Sales,
Month(Date) AS Month,
Year(Date) AS Year,
Date
Resident Date
Where Year(Date)=$(vyear) and num(Month(Date))=$(vMonthNo);
STORE Sales into C:\QVDS\\$(vyear)_$(vMonthName).qvd(qvd);
NEXT
NEXT
DROP Table Date,Month,Year;
EXIT Script;
any help can appreciate.
Thanks
John
How about making this a bit simpler? Imagine that you have a resident table with a Date and Sales field already. You can use AddMonths() and InMonth() functions to check for records that should go in the current file, like in:
LET vcurrentdate= num(MonthStart(AddYears(num(Today()),-3))); // Set start date
DO WHILE vcurrentdate <= Today()
ExportSales:
NOCONCATENATE
LOAD *
RESIDENT Sales
WHERE InMonth(Date, $(vcurrentdate), 0) = true();
LET vdatestring = Date($(vcurrentdate), 'YYYY_MMM');
// STORE Sales INTO
DROP Table ExportSales;
LET vcurrentdate = num(AddMonths($(vcurrentdate), 1));
LOOP
See also document in attachment.
Best,
Peter
How about making this a bit simpler? Imagine that you have a resident table with a Date and Sales field already. You can use AddMonths() and InMonth() functions to check for records that should go in the current file, like in:
LET vcurrentdate= num(MonthStart(AddYears(num(Today()),-3))); // Set start date
DO WHILE vcurrentdate <= Today()
ExportSales:
NOCONCATENATE
LOAD *
RESIDENT Sales
WHERE InMonth(Date, $(vcurrentdate), 0) = true();
LET vdatestring = Date($(vcurrentdate), 'YYYY_MMM');
// STORE Sales INTO
DROP Table ExportSales;
LET vcurrentdate = num(AddMonths($(vcurrentdate), 1));
LOOP
See also document in attachment.
Best,
Peter
hi john,
Try:
Temp:
LOAD Distinct MonthName(MonthStart(Date)) AS Month
FROM Table
ORDER BY Date;
FOR i= 1 to NoOfRows('Temp')
LET vMonth = Peek('Month', $(i), 'Temp');
MonthWise:
NoConcatenate
LOAD * FROM Table
Where MonthName(Date) = '$(vMonth)';
STORE MonthWise into TableName_$( vMonth).qvd;
DROP Table MonthWise;
Next
DROP Table Temp;
Thanks Peter, you helped me a lot.
Edited: if possible could you please explain how the code will work
After the following, the code should be self-explaining.
Best,
Peter
BTW this simple version doesn't check for missing data (e.g. a particular month may have no sales records). If you do not want any empty QVD's to be generated, you can use NoOfRows() to check whether the current ExportSales table has any rows at all, and skip the STORE if there aren't any.
Peter
Thanks Peter Cammaert,
Instead of InMonth, if I use InMonthTodate, it will check for that date only right?
Edited: Peter for the same data I want to generate qvds period wise, if the period1 starts May 25th every year and the last period ends at May 24th next year how do I do it? Each period has 28 days. Total 13 Periods for one complete year.
Best
John
Peter
Thanks neetha.