Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have below qvd files in my library location. I only want to load data from 2018_05 to rest all. How to transform my existing code(Given below)?
AUXFILE_2018_03.qvd
AUXFILE_2018_04.qvd
AUXFILE_2018_05.qvd
AUXFILE_2018_06.qvd
AUXFILE_2018_07.qvd
AUXFILE_2018_08.qvd
AUXFILE_2018_09.qvd
AUXFILE_2018_10.qvd
AUXFILE_2018_11.qvd
AUXFILE_2018_12.qvd
AUXFILE_2019_01.qvd
AUXFILE_2019_02.qvd
AUXFILE_2019_03.qvd
AUXFILE_2019_04.qvd
AUXFILE_2019_05.qvd
AUXFILE_2019_06.qvd
****My Code****
Set vStartYear = 2018;
Let vCurrentYear= year(now());
For vYear = $(vStartYear) to $(vCurrentYear)
DTA:
LOAD
ID,
Region,
Country
FROM [lib://CID/AUXFILE_$(vYear)*.qvd]
(qvd);
store DTA into [lib://STR/DTA_$(vYear).qvd));
next vYear
Hi,
maybe something like this could work:
Set vStartYear = 2018;
Set vStartMonth = 5;
Let vCurrentYear= Year(Now());
Let vStartQvd = Date(MakeDate($(vStartYear),$(vStartMonth)),'YYYYMM');
FOR Each File in FileList('[lib://CID/AUXFILE_*.qvd]')
TABLE_temp:
LOAD
SubField(Mid('$(File)',Index('$(File)','AUXFILE_')),'_',2) &
Left(SubField(Mid('$(File)',Index('$(File)','AUXFILE_')),'_',3),2) as YearMonth,
SubField(Mid('$(File)',Index('$(File)','AUXFILE_')),'_',2) as Year,
'$(File)' as File
AutoGenerate 1;
NEXT
For vYear = '$(vStartYear)' to '$(vCurrentYear)'
NoConcatenate
TABLE_$(vYear):
LOAD File
Resident TABLE_temp
Where YearMonth>$(vStartQvd) and Year=$(vYear);
FOR i=0 to NoOfRows('TABLE_$(vYear)')-1
LET vQvd = Peek('File',$(i),'TABLE_$(vYear)');
DTA_$(vYear):
LOAD
ID,
Region,
Country
From $(vQvd) (qvd);
STORE DTA_$(vYear) into DTA_$(vYear);
DROP Table DTA_$(vYear);
NEXT
NEXT
DROP Table TABLE_temp;
Can anyone please help on this
Hi,
maybe something like this could work:
Set vStartYear = 2018;
Set vStartMonth = 5;
Let vCurrentYear= Year(Now());
Let vStartQvd = Date(MakeDate($(vStartYear),$(vStartMonth)),'YYYYMM');
FOR Each File in FileList('[lib://CID/AUXFILE_*.qvd]')
TABLE_temp:
LOAD
SubField(Mid('$(File)',Index('$(File)','AUXFILE_')),'_',2) &
Left(SubField(Mid('$(File)',Index('$(File)','AUXFILE_')),'_',3),2) as YearMonth,
SubField(Mid('$(File)',Index('$(File)','AUXFILE_')),'_',2) as Year,
'$(File)' as File
AutoGenerate 1;
NEXT
For vYear = '$(vStartYear)' to '$(vCurrentYear)'
NoConcatenate
TABLE_$(vYear):
LOAD File
Resident TABLE_temp
Where YearMonth>$(vStartQvd) and Year=$(vYear);
FOR i=0 to NoOfRows('TABLE_$(vYear)')-1
LET vQvd = Peek('File',$(i),'TABLE_$(vYear)');
DTA_$(vYear):
LOAD
ID,
Region,
Country
From $(vQvd) (qvd);
STORE DTA_$(vYear) into DTA_$(vYear);
DROP Table DTA_$(vYear);
NEXT
NEXT
DROP Table TABLE_temp;