How to generate monthly QVD, i have my loading script, but not working

[jim_chan]

Dear all, 

Please help. my intention is(example)

1) i have 202209 and 202210 daily qvd file as example. It suppose to be 30 -31 daily files per month. 

2) i want to create a MONTHLY sum_sale_202209.qvd and a MONTHLY sum_sale_202210.qvd.

3) But my script below, dont seem to be correct, no data in these 2 files.

4) And this is my script, can anyone help to identify where's the issue and how to resolve it?

MyQvd:
LOAD *,

Date( Date#(Subfield(FileBaseName(), '_',-1),'YYYYMMDD'), 'YYYYMM') as YearMonth

FROM [lib://QVD/SUM_SALES_*.qvd](qvd);

Temp:
Load Distinct Date(YearMonth,'YYYYMM') as YearMonth
Resident MyQvd;

let Vm=NoOfRows('Temp');

for I=0 to $(Vm)-1;
LET vFileName=Peek('YearMonth',$(I),'Temp');

$(vFileName):

NoConcatenate
Load *
Resident MyQvd
where YearMonth = '$(vFileName)';

STORE $(vFileName) INTO [lib://QVD/SUM_SALES_$(vFileName).qvd](QVD);

drop table $(vFileName);
NEXT I;

DROP Table Temp, MyQvd;

exit script;

[Vegar]

Try this:

MyQvd:

LOAD *,

Date( monthstart( Date#(Subfield(FileBaseName(), '_',-1),'YYYYMMDD') ), 'YYYYMM') as YearMonth

FROM [lib://QVD/SUM_SALES_*.qvd](qvd);

Temp:

Load Distinct Date(YearMonth,'YYYYMM') as YearMonth

Resident MyQvd;

let Vm=NoOfRows('Temp');

for I=0 to $(Vm)-1;

LET vFileName=Peek('YearMonth',$(I),'Temp');

$(vFileName):

NoConcatenate

Load *

Resident MyQvd

where YearMonth = '$(vFileName)';

STORE $(vFileName) INTO [lib://QVD/SUM_SALES_$(vFileName).qvd](QVD);

drop table $(vFileName);

NEXT I;

DROP Table Temp, MyQvd;

exit script;

[jim_chan]

hi there. does this work if i have 20220901, 20220902, 03,04,05 onwards???  

[jim_chan]

i have change to MonthSTART. but the result is COMBINED, combining  09 10  data into 2 different mothly file. you see.

[jim_chan]

i change to Monthstart and uncomment the where clause - where YearMonth = '$(vFileName)';  

after loaded the script. it returns me back with 0 value in this 2 monthly files. 

Rgds

Jim

[Vegar · Accepted solution]

It is the comparison  where YearMonth = '$(vFileName)' that is causing you trouble.

See my script below. 

MyQvd:
LOAD *,
	Date( monthstart( Date#(Subfield(FileBaseName(), '_',-1),'YYYYMMDD') ), 'YYYYMM') as YearMonth
FROM 
	[$(vL.QVDPath)JimChan/SUM_SALES_????????.qvd]  //??????? to avoid reading the output files that also fits the * pattern
	(qvd);

Temp:
Load Distinct Date(YearMonth,'YYYYMM') as YearMonth
Resident MyQvd;

let Vm=NoOfRows('Temp');

for I=0 to $(Vm)-1;
	LET vFileName=Peek('YearMonth',$(I),'Temp');
	
	[$(vFileName)]:
	NoConcatenate
	Load *
	Resident MyQvd
	Where text(YearMonth) = '$(vFileName)'
	;
	
	STORE $(vFileName) INTO [$(vL.QVDPath)JimChan/SUM_SALES_$(vFileName).qvd](QVD);
	drop table $(vFileName);
NEXT I;

DROP Table Temp, MyQvd;

[jim_chan]

but i have whole moth of daily files. from day 1 - 30 of sep? how to make make it load all if i dont use * ?

[Vegar]

Maybe you can make the name of your output files a bit different so they don't fit into your SUM_SALES_* pattern.