topic Re: Error with "For i = 0 to (vNumberOfRows - 1)" in Connectivity & Data Prep

Error with "For i = 0 to (vNumberOfRows - 1)"

hammermill21 — Tue, 21 Dec 2021 20:12:36 GMT

Hello!

I have the following script:

[Variables-Formulas]:
LOAD
[Variable-Formula],
Expression,
Usage
FROM [lib://Matrix Variables-Formulas (uhealth_54171224)/Matrix Variables-Formulas.xlsx]
(ooxml, embedded labels, table is [Variables-Formulas]);
LET vNumberOfRows = NoOfRows('Variables-Formulas');
For i = 0 to (vNumberOfRows - 1)
LET vVariable_Name = Peek('Variable-Formula',i,'Expression');
LET [$(vVariable_Name)] = Peek('Expression',i,'Expression');
Next

NullAsValue "UMHC SCCC",
"UHealth Tower",
"UMHC BPEI",
"UMHC LFMC",
"UMHC Radiology",
"UMHC Applebaum",
"UMHC Ear Institute",
"UMHC Pain Clinic",
"UMHC Safie",
"UMHC IR Clinic",
"UMHC Coral Spring",
"UMHC Deerfield",
"UMHC Hollywood",
"UMHC Kendall",
"UMHC Plantation",
"UMHC Marlin Park";
SET NullValue = '0';

[Map EC-LS-EM]:
MAPPING Load *
Inline [
EC-LS-EM, Description
EC, Environment of Care
LS, Life Safety
EM, Emergency Management
];

Which had been working fine but now I am getting this error:

The following error occurred:

The error occurred here:

For i = to

I am not really sure what is happening.

Any ideas?

THANK YOU!

Re: Error with "For i = 0 to (vNumberOfRows - 1)"

Nicole-Smith — Tue, 23 Jul 2019 18:54:17 GMT

You need to use dollar sign expansion around the variable name:

For i = 0 to ($(vNumberOfRows) - 1)

Re: Error with "For i = 0 to (vNumberOfRows - 1)"

hammermill21 — Tue, 23 Jul 2019 18:57:32 GMT

Hi,

When I do that, I get this error:

The following error occurred:

Unexpected token: ')', expected one of: '(', 'ZTestw_z', 'OPERATOR_PLUS', 'OPERATOR_MINUS', 'not', 'bitnot', 'LITERAL_NUMBER', ...

The error occurred here:

For i = 0 to (>>>>>>)<<<<<<

Re: Error with "For i = 0 to (vNumberOfRows - 1)"

Nicole-Smith — Tue, 23 Jul 2019 19:03:01 GMT

That error is coming from the values you are trying to store in vVariable_Name and [$(vVariable_Name)]. Change those from LET to SET.

(And make sure to leave the dollar sign expansion on the FOR.)

Re: Error with "For i = 0 to (vNumberOfRows - 1)"

hammermill21 — Tue, 23 Jul 2019 19:04:24 GMT

Ok So I changed them to SET:

For i = 0 to ($(vNumberOfRows - 1))
SET vVariable_Name = Peek('Variable-Formula',i,'Expression');
SET [$(vVariable_Name)] = Peek('Expression',i,'Expression');
Next

And I still get that error? 😥

Re: Error with "For i = 0 to (vNumberOfRows - 1)"

Nicole-Smith — Tue, 23 Jul 2019 19:16:12 GMT

You have some incorrect arguments in your PEEK functions. The third argument should be table name:

[Variables-Formulas]:
LOAD
[Variable-Formula],
Expression,
Usage
FROM [lib://Matrix Variables-Formulas (uhealth_54171224)/Matrix Variables-Formulas.xlsx]
(ooxml, embedded labels, table is [Variables-Formulas]);
LET vNumberOfRows = NoOfRows('Variables-Formulas');
For i = 0 to ($(vNumberOfRows) - 1)
LET vVariable_Name = Peek('Variable-Formula',i,'Variables-Formulas');
LET [$(vVariable_Name)] = Peek('Expression',i,'Variables-Formulas');
Next

And leave them as LET statements--the PEEKs won't evaluate otherwise. I missed that previously.

Re: Error with "For i = 0 to (vNumberOfRows - 1)"

hammermill21 — Wed, 24 Jul 2019 12:24:29 GMT

Hi thank you, but that is still causing me an error:

The following error occurred:

Unexpected token: ')', expected one of: '(', 'ZTestw_z', 'OPERATOR_PLUS', 'OPERATOR_MINUS', 'not', 'bitnot', 'LITERAL_NUMBER', ...

The error occurred here:

For i = 0 to (>>>>>>)<<<<<<

Re: Error with "For i = 0 to (vNumberOfRows - 1)"

Nicole-Smith — Wed, 24 Jul 2019 15:03:32 GMT

This example works:

[Variables-Formulas]:
LOAD [Variable-Formula],
[Expression],
[Usage]
INLINE [
Variable-Formula, Expression, Usage
Test 1, min(Usage), 10
Test 2, max(Usage), 20
];
LET vNumberOfRows = NoOfRows('Variables-Formulas');
For i = 0 to ($(vNumberOfRows) - 1)
LET vVariable_Name = Peek('Variable-Formula',i,'Variables-Formulas');
LET [$(vVariable_Name)] = Peek('Expression',i,'Variables-Formulas');
Next
SET vNumberOfRows =;
SET i =;
SET vVariable_Name =;

Can you share the Excel file? It has to do with the values in there that you're trying to pass into the variables.

Re: Error with "For i = 0 to (vNumberOfRows - 1)"

hammermill21 — Wed, 24 Jul 2019 15:08:41 GMT

Yes of course, I have attached it here.

It is just strange since I haven't changed anything on that sheet.

Thank you for all your help!

Re: Error with "For i = 0 to (vNumberOfRows - 1)"

Nicole-Smith — Wed, 24 Jul 2019 15:24:47 GMT

This code works fine for me when I run it (no errors and it creates all variables):

[Variables-Formulas]: LOAD [Variable-Formula], [Expression], [Usage] FROM [lib://Desktop/Matrix Variables-Formulas.xlsx] (ooxml, embedded labels, table is [Variables-Formulas]); LET vNumberOfRows = NoOfRows('Variables-Formulas'); For i = 0 to ($(vNumberOfRows) - 1) LET vVariable_Name = Peek('Variable-Formula',i,'Variables-Formulas'); LET [$(vVariable_Name)] = Peek('Expression',i,'Variables-Formulas'); Next SET vNumberOfRows =; SET i =; SET vVariable_Name =;

What version are you running? I'm using Desktop April 2019.

Re: Error with "For i = 0 to (vNumberOfRows - 1)"

hammermill21 — Wed, 24 Jul 2019 16:46:34 GMT

I am running a much older version.

I'm going to see if they can update it for me, maybe that is what the problem is.

Re: Error with "For i = 0 to (vNumberOfRows - 1)"

hammermill21 — Wed, 24 Jul 2019 17:25:16 GMT

So I updated to April 2019 and I am still getting that error 😞 I attached the qvf, I'm not really sure what else it can be?

Re: Error with "For i = 0 to (vNumberOfRows - 1)"

Nicole-Smith — Wed, 24 Jul 2019 18:07:10 GMT

The whole "Governed Metrics" code piece runs fine for me without any errors, and I see the variables have all been defined correctly:

You may need to reach out to Qlik Support because I have no idea why it would be working on my machine but not yours.