Variables in FOR loop

[#1]

I have a file raw_data.qvd that already exists, and I am trying to do an incremental load to add new excel files to the qvd.  It works fine if I do a loop from 1 to a variable, but not for a variable to a variable.  So how do I get it to work so I don't have to start with a fixed number all the time?

When it runs, I get this error - notice the first variable is blank:

Script line error:
FOR a= to 9

This is what I end up with in my data table for the variables, so I know they are resolving correctly:

$(N)   $(Z)
9        8

For this example let's say I have 7 files already loaded, and there are two new ones (8 & 9) that need to be loaded. I want the FOR to end up being "FOR a=8 to 9"

FOR a=$(Z) to $(N);

// ^ this doesn't work - $(Z) comes up as blank in the FOR but loads the expected value in the LOAD later on

FOR a=1 to $(N);

// ^ this works fine, but I don't want to start at 1.  The $(N) variable works fine in both examples

Let FileName = 'PATH\TEST_$(a).xlsx';
// $(a) is a variable representing the numbered file name aka TEST_1.xlsx, TEST_1.xlsx, etc.

data:
LOAD *
, $(Z) as var_z
, $(N) as var_n

, $(a) as var_a
FROM
$(FileName)
(ooxml, embedded labels, table is Sheet1);
Next
.

.

.

[jonathandienst]

Hi

The problem is before the loop, as Z is not being determined before the loop start. Test this by forcing Z to a value of 8 before the loop, or use a TRACE Z = $(Z) command before the loop starts. You could also step through the script in debug mode to see what is happening when you calculate Z.

HTH

Jonathan

[#3]

I set the Z variable earlier, just didn't include it in the example.  When I get my data table in the end, the Z variable is populated.

[#4]

Hi,

Can you show your script of assigning value to variable Z? Are you using SET or LET?

I guess something is going wrong in assigning statement, the value for Z that you are seeing after the script execution might be previously loaded value.

[jonathandienst · Accepted solution]

Christine

I think you are mistaken - it seems that Z is not populated (at least not with a number) when the FOR statement is executed . What Z is at the end of the script is not the point. If I were solving this problem, I would step through the code before the loop in the debugger, and see what Z is doing.

Regards

Jonathan

[sasikanth]

Hi Christine Hill

There is no problem with u r code ...but Try this replace  function in place of Z variable like NoOfRows('tablename')

For a=$(n) to NoOfrows('tablename')

I am not sure but may help you

[#7]

Yeah, you guys are right!  I don't know how the variable was making it to the end ok since I was only setting it in one place.  I tweaked it a little and got it to run.  Thanks!