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: 
amit_saini
Master III
Master III

Script Error Help

Hi Folks,

Is this right way of defining a loop????

For each vSheet in 'OEE','Line 1','Line 2','Line 3'

Plant:

LOAD

    left(B,3) as Plant

FROM

[amit.xlsx]

(ooxml, no labels, table is [Data $(vSheet)])

where RowNo() < 1;

Thanks,

AS

1 Solution

Accepted Solutions
tresesco
MVP
MVP


Then try like:

For each vSheet in '(OEE)',('Line 1)',('Line 2)','(Line 3)'   // parenthesis

Plant:

LOAD

    left(B,3) as Plant

FROM

[amit.xlsx]

(ooxml, no labels, table is [Data$(vSheet)])                    // No space

where RowNo() < 1;


NEXT vSheet

View solution in original post

9 Replies
tresesco
MVP
MVP

You are missing a 'NEXT' at the end.

alexandros17
Partner - Champion III
Partner - Champion III

You missed the NEXT at the end

For each vSheet in 'OEE','Line 1','Line 2','Line 3'

Plant:

LOAD

    left(B,3) as Plant

FROM

[amit.xlsx]

(ooxml, no labels, table is [Data $(vSheet)])

where RowNo() < 1;

NEXT

amit_saini
Master III
Master III
Author

Hi Tresesco,

I do have Next at the bottom which I missed , My concern here whether this [Data $(vSheet)] is fine or not????

Not applicable

What is Data for?

Is your sheet name Data Line 1? If your sheet name is simply OEE, Line 1, Line 2... etc.

Use

For each vSheet in 'OEE','Line 1','Line 2','Line 3'

Plant:

LOAD

    left(B,3) as Plant

FROM

[amit.xlsx]

(ooxml, no labels, table is [$(vSheet)])

where RowNo() < 1;

NEXT vSheet

tresesco
MVP
MVP

It seems fine. You might check for appropriation of space in the sheet name.

Peter_Cammaert
Partner - Champion III
Partner - Champion III

Yes it will work, just try it and you'll see. On condition that the sheets are named 'Data OEE', 'Data Line 1' and so on.

What happens is this: before each successive execution of the LOAD statement, the script engine will pre-scan the text of the LOAD statement (including the table name and semicolon), and replace all occurrences of dollar substitution strings with their respective values. The funny thing is, you can replace everything with a $(name) tag, even the table name, the LOAD keyword and the semicolon. Not that it serves any use

Peter

amit_saini
Master III
Master III
Author

I'm having one excel sheet with name as Data(OEE), Data(Line 1)....and so on

Thanks

AS

Not applicable

Use

For each vSheet in 'OEE','Line 1','Line 2','Line 3'

Plant:

LOAD

    left(B,3) as Plant

FROM

[amit.xlsx]

(ooxml, no labels, table is [Data($(vSheet))])

where RowNo() < 1;

NEXT vSheet

tresesco
MVP
MVP


Then try like:

For each vSheet in '(OEE)',('Line 1)',('Line 2)','(Line 3)'   // parenthesis

Plant:

LOAD

    left(B,3) as Plant

FROM

[amit.xlsx]

(ooxml, no labels, table is [Data$(vSheet)])                    // No space

where RowNo() < 1;


NEXT vSheet