topic Re: Variables in FOR loop in QlikView

Variables in FOR loop

Thu, 31 Oct 2013 05:23:41 GMT

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
.

Re: Variables in FOR loop

jonathandienst — Thu, 31 Oct 2013 05:34:58 GMT

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

Re: Variables in FOR loop

Thu, 31 Oct 2013 05:37:36 GMT

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.

Re: Variables in FOR loop

Thu, 31 Oct 2013 05:50:35 GMT

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.

Re: Variables in FOR loop

jonathandienst — Thu, 31 Oct 2013 05:51:23 GMT

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

Re: Variables in FOR loop

sasikanth — Thu, 31 Oct 2013 05:55:33 GMT

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

Re: Variables in FOR loop

Thu, 31 Oct 2013 06:23:12 GMT

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!