Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
.
.
.
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
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
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.
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.
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
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
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!