Monthly Qvd

[#1]

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

[Peter_Cammaert · Accepted solution]

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 (qvd); // Your export file name
  STORE ExportSales INTO [.\$(vdatestring).QVD] (qvd); // Mine

  DROP Table ExportSales;

  LET vcurrentdate = num(AddMonths($(vcurrentdate), 1));

LOOP

See also document in attachment.

Best,

Peter

[Anonymous]

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;

[#4]

Thanks Peter, you helped me a lot.

Edited: if possible could you please explain how the code will work

[Peter_Cammaert]

After the following, the code should be self-explaining.

• Instead of precalculating a MonthYear table, we define a starting date and use a loop to successively add a single month to this date.
• The loop stops when we reach the current month (using Today())
• Every cycle consists of an extraction (into a separate table called ExportSales) of all rows whose date falls in the current YearMonth loop period. We use InMonth() to check whether a date falls in the month of a basedate.
• From the current loop date, we calculate a YearMonth string representation and save the new export table to a file with a name using that string representation.
• Next we drop the export table. It will be recreated with different data during the next loop cycle.
• Next we add a single month to the loop variable and start over again.

Best,

Peter

[Peter_Cammaert]

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

[#7]

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_Cammaert]

1. No not really. XXXToDate usually means "From the very start of XXX until the current date". While InMonth will simply check whether a specific date falls in the specified month (may include future dates), InMonthToDate will check whether a date is between the start of a particular month, and the current day number of that particular month (the 4th in our case). InYearToDate checks for  Jan 1, 2015 to Sep 4, 2015. InYear checks whether a date lies in 2015.
   See QV Help - Date and Time functions for an explanation and various examples.
2. Edited... Better start a new discussion before we move entirely off-topic.

Peter

[#9]

Thanks neetha.