topic Re: Script Error Help in QlikView

Script Error Help

amit_saini — Tue, 04 Mar 2014 08:28:28 GMT

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,

Re: Script Error Help

tresB — Tue, 04 Mar 2014 08:32:04 GMT

You are missing a 'NEXT' at the end.

Re: Script Error Help

alexandros17 — Tue, 04 Mar 2014 08:32:06 GMT

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;

Re: Script Error Help

amit_saini — Tue, 04 Mar 2014 08:39:24 GMT

Hi Tresesco,

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

Re: Script Error Help

Tue, 04 Mar 2014 08:41:44 GMT

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

Re: Script Error Help

tresB — Tue, 04 Mar 2014 08:48:35 GMT

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

Re: Script Error Help

Peter_Cammaert — Tue, 04 Mar 2014 08:50:29 GMT

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

Re: Script Error Help

amit_saini — Tue, 04 Mar 2014 08:57:41 GMT

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

Thanks

Re: Script Error Help

Tue, 04 Mar 2014 09:02:36 GMT

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

Re: Script Error Help

tresB — Tue, 04 Mar 2014 09:03:21 GMT

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