Load from Specific Yearmonth

[farahahmad]

 Hi I have a question.

I need to load file from a specific yearmonth to last month yearmonth.

Below is my script. However it will have error that it cannot find '201813' file. I believe this is because it read the vDateFrom as a text. I did try using the and vDateFrom = Date('201801','YYYYMM').

LET vDateFrom = '201812';
LET vDateTo  = date(AddMonths(date(today()),-1),'YYYYMM');

for b = $(vDateFrom) to $(vDateTo)

AllFiles:
LOAD
     Contract as %Contract,
     [Net order value] as SpendValue

FROM [lib://SPEND SOURCE/spend$(b).xlsx]
(ooxml, embedded labels, table is spend);

Store AllFiles into [lib://SPEND SOURCE/T1_SPEND_YTD_MainFile.qvd](qvd);

Drop Table AllFiles;

I also did refer to this post but also failed : https://community.qlik.com/t5/QlikView-Scripting/for-loop-in-qlikview-with-dates/td-p/430522

Thanks

Next;

[jonathandienst · Accepted solution]

Like this:

Let vDate = Date#('201812', 'yyyyMM');

AllFiles:
LOAD 0 as %Contract Autogenerate 0;

Do
	Concatenate(AllFiles)
	LOAD Contract as %Contract,
		[Net order value] as SpendValue
	FROM [lib://SPEND SOURCE/spend$(vDate).xlsx]
	(ooxml, embedded labels, table is spend);
	
	Let vDate = Date(AddMonths(vDate, 1), 'yyyyMM');
Loop While vDate < Today()

Store AllFiles into [lib://SPEND SOURCE/T1_SPEND_YTD_MainFile.qvd](qvd);
Drop Table AllFiles;

[farahahmad]

Hi Jontydkpi,

Thanks it work. But can you explain why we need to load the 

LOAD 0 as %Contract Autogenerate 0;

then do the loop?

Thanks. 

[jonathandienst]

The Autogenerate 0 creates the table structure with 0 rows,  so I can concatenate into it. Using a forced concatenation means that the reload won't break if an extra column is added to one of the spreadsheets. Its not strictly necessary, but its a good practice design pattern that I use for looped loads.

[farahahmad]

Hi,

Sorry for the late reply.

Thanks for the explanation.

Regards.