Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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;
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;
hi there. does this work if i have 20220901, 20220902, 03,04,05 onwards???
i have change to MonthSTART. but the result is COMBINED, combining 09 10 data into 2 different mothly file. you see.
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
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;
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 * ?
Maybe you can make the name of your output files a bit different so they don't fit into your SUM_SALES_* pattern.