How to create a monthly qvd based on date field in data?

[jim_chan]

Hello there, 

i  came through a script as below to create a monthly qvd based on date field in data. But i start wondering 

the vMonth actually give me a a month number from 01-12. for 2019 this year should be no problem producing out the qvd by 201901- until 201912. 

But what if next year 2020, my data file comes with 202001. will the script below inteliigently able to help e split the file to 201901 and 202001 qvd?

Sample script would be like below.

Month:

Load Distinct Month(Date) as MONTH From XYZ;

Let vCount = noofrows('Month');

For i=0 to $(vCount) -1

     Let vMonth = peek('MONTH',$(i),'Month');

     $(vMonth):

     Load * from XYZ where Month(Date) = $(vMonth);

     Store $(vMonth) into $(vMonth).qvd;

     Drop Table $(vMonth);

Next

[zhadrakas · Accepted solution]

No. You need to create at least a year variable. I also added YearMonth Variable.

Month:
Load Distinct Year(Date) & Month(Date) as YEARMONTH From XYZ;
Let vCount = noofrows('Month');
For i=0 to $(vCount) -1
     LET vYearMonth = peek('YEARMONTH ',$(i),'Month');
     LET vYear = left($(vYearMonth),4);
     LET vMonth = right($(vYearMonth),2);

     $(vYearMonth):
     Load * from XYZ where Month(Date) = $(vMonth) AND year(Date) = $(vYear );
     Store $(vYearMonth) into $(vYearMonth).qvd;
     Drop Table $(vYearMonth);
next i

regards

tim

[jim_chan]

Hi . i try to run with your script, but turns out has token error.

This is my original script, which are working fine. but, when i changed to your script, it didnt work out and has error. How can i apply your script to my script?

Month:
Load Distinct
num(Month(BIOMETRIC_CTOS_DT), '00')as NumMonth
From
D:\DAILY_REPORT_HIST_1.qvd(qvd);

Let vCount = noofrows('Month');

For i=0 to $(vCount) -1
Let y = If($(x) = 0, 0, $(x) * -1);
Let vMonth = peek('NumMonth',$(i),'Month');
Let vYYYY = Year(MonthStart(AddMonths((Date(Today (), 'DD/MM/YYYY')), $(y))));

$(vMonth):

Load * from
D:\HIST.qvd(qvd)
where Month(BIOMETRIC_DT) = $(vMonth);

Store $(vMonth) into D:\DAILY_REPORT_$(vYYYY)$(vMonth).qvd;

Drop Table $(vMonth);

Next

[zhadrakas]

Try this:

Month:
Load Distinct
num(Month(BIOMETRIC_CTOS_DT), '00')as NumMonth
From
D:\DAILY_REPORT_HIST_1.qvd(qvd);

Let vCount = noofrows('Month');

For i=0 to $(vCount) -1
Let y = If($(x) = 0, 0, $(x) * -1);
Let vMonth = peek('NumMonth',$(i),'Month');
Let vYYYY = Year(MonthStart(AddMonths((Date(Today (), 'DD/MM/YYYY')), $(y))));

$(vMonth):

Load * from
D:\HIST.qvd(qvd)
where Month(BIOMETRIC_DT) = $(vMonth) AND year(BIOMETRIC_DT) = $(vYYYY);

Store $(vMonth) into D:\DAILY_REPORT_$(vYYYY)$(vMonth).qvd;

Drop Table $(vMonth);

Next

i just added this line  before the store statement:

AND year(BIOMETRIC_DT) = $(vYYYY) 

regards

tim

[jim_chan]

This is perfect. By adding that year in where clause. will it solve my 201901 and 202001 issue?? 

[zhadrakas]

thats exactly what this line does. 

[jim_chan]

I only have this 4 months of data. i dont have 2018 or 2020 data. haha. but thanks! Great Help from there!i will mark your suggestion as the solution, instead of mine 🙂 .

Thank you!

Rgds

Jim