Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
farahahmad
Contributor II
Contributor II

Load from Specific Yearmonth

 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;

 

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

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;

 

 

 

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

4 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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;

 

 

 

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
farahahmad
Contributor II
Contributor II
Author

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
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
farahahmad
Contributor II
Contributor II
Author

Hi,

 

Sorry for the late reply.

 

Thanks for the explanation.

 

Regards.