Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
This is perfect. By adding that year in where clause. will it solve my 201901 and 202001 issue??
thats exactly what this line does.
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