topic Re: How to create a monthly qvd based on date field in data? in QlikView

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

jim_chan — Sat, 16 Nov 2024 01:47:57 GMT

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);

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

zhadrakas — Tue, 03 Dec 2019 12:31:10 GMT

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

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

jim_chan — Tue, 03 Dec 2019 12:48:38 GMT

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);

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

zhadrakas — Tue, 03 Dec 2019 12:53:01 GMT

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

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

jim_chan — Tue, 03 Dec 2019 12:59:56 GMT

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

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

zhadrakas — Tue, 03 Dec 2019 13:02:57 GMT

thats exactly what this line does.

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

jim_chan — Tue, 03 Dec 2019 13:05:23 GMT

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