Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Announcement: Certain actions are currently causing a Page Not Found error. This is a known issue and we are working with the platform vendor to investigate and resolve it.
maxsugak
New Contributor

FOR each... loop in script

Hi,

I'm trying to load multiple Excel sheets from 1 file, using the FOR loop.

But got a mistake when implemeting it:

FOR Each sheet in 'Sheet 1', 'Sheet 2' 

[Test]:
LOAD F1 as ValueType, 
     F2 as Brand
FROM
[Excel.xls]
(biff, embedded labels, header is 4 lines, table sheet)
NEXT

I think the problem is in sheet naming there - 

(biff, embedded labels, header is 4 lines, table sheet)

 

Any suggestions please? 

Labels (4)
1 Solution

Accepted Solutions
pradosh_thakur
Honored Contributor II

Re: FOR each... loop in script

(biff, embedded labels, header is 4 lines, table is [$(sheet) & '$'])
or

(biff, embedded labels, header is 4 lines, table is [$(sheet & '$')])

Learning never stops.
5 Replies
pradosh_thakur
Honored Contributor II

Re: FOR each... loop in script

Try

(biff, embedded labels, header is 4 lines, table is [$(sheet)])


Have a look.

https://community.qlik.com/t5/QlikView-Documents/Loading-Multiple-Excel-Sheets-Dynamically-along-wit...
Learning never stops.
maxsugak
New Contributor

Re: FOR each... loop in script

Thanks,

I've tried but got error Cannot locate table in BIFF file

pradosh_thakur
Honored Contributor II

Re: FOR each... loop in script

(biff, embedded labels, header is 4 lines, table is [$(sheet) & '$'])
or

(biff, embedded labels, header is 4 lines, table is [$(sheet & '$')])

Learning never stops.
pradosh_thakur
Honored Contributor II

Re: FOR each... loop in script

Try Below
FOR Each sheet in 'Sheet 1$', 'Sheet 2$'

[Test]:
LOAD F1 as ValueType,
F2 as Brand
FROM
[Excel.xls]
(biff, embedded labels, header is 4 lines, table is [$(sheet)] )
NEXT
Learning never stops.
Highlighted
maxsugak
New Contributor

Re: FOR each... loop in script

The second one works, thank you!