Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Error with FOR loop

I am having a very odd error when using a FOR loop.

Basically I have a table of 7 filenames and the idea is to set a variable with the filename and then use the filename in a from statement. For the purposes of testing I have just been attempting to run around the variable being set.

The loop works fine right uptil the 7 value being pulled then it tries to loop again and then errors with a Next error

The script I am using is:

**************************************************************************************************************************************

LET vRowCount = fieldvaluecount('FileNameA');

Trace vRowCount = $(RowCount);

FOR i = 1 TO $(vRowCount)

          Trace i=$(i);

          Trace LOOP;

          LET vLoopFileName = FieldValue('FileNameA',$(i));//Peek('FileNameA',i,'BaseDataFilenames');

          Trace vLoopFileName = $(vLoopFileName);

EXIT FOR when $(i) =7

NEXT

*********************************************************************************************************************************

I have also attempted the same loop but substituting fieldvaluecount with RowNum and FieldValue with a peek...

Error wording is: 

Script line error:

Next

7 Replies
Not applicable
Author

your script works perfectly for me in QV10.  Only a tiny change to display vRowCount in line two.  Your "v" was missing.  Trace vRowCount = $(vRowCount);

load * inline [

FileNameA

a

b

c

d

e

f

g

h

i

j

k

];

LET vRowCount = fieldvaluecount('FileNameA');

Trace vRowCount = $(vRowCount);

FOR i = 1 TO $(vRowCount)

          Trace i=$(i);

          Trace LOOP;

          LET vLoopFileName = FieldValue('FileNameA',$(i));//Peek('FileNameA',i,'BaseDataFilenames');

          Trace vLoopFileName = $(vLoopFileName);

EXIT FOR when $(i) =7

NEXT

************* output****************

INL97E7 11 lines fetched

vRowCount = 11

i=1

LOOP

vLoopFileName = a

i=2

LOOP

vLoopFileName = b

i=3

LOOP

vLoopFileName = c

i=4

LOOP

vLoopFileName = d

i=5

LOOP

vLoopFileName = e

i=6

LOOP

vLoopFileName = f

i=7

LOOP

vLoopFileName = g

--- Script Finished ---

Not applicable
Author

todwith1d,

We are also using QV10 however it's release 1 rather than 2.....

Strangely when I paste your above syntax into a clean document it works perfectly but continues to fail in it's original source.

Is it possible a script can become corrupt? or alternatively could the cause be that the table being referenced in the Loop is on a different tab??

Cheers

Kris

Not applicable
Author

I have seen the behavior you mention before.  It can be tough to pin down so if you have not put your script in a new file, it is worth a try.

If it is still an issue, send the filename file or tell us how you are genrating the list of filenames so we can more closely replicate your script.


TD

Not applicable
Author

TD,

This is the prior bit of script that is generating the table of filenames.

// This will get a list of all data files available in the drive loaction for loading:

BaseDataFilenames_Temp:

Load distinct filename() as FileNameA,

   MakeDate('20'&MID(filebasename(),17,2),Mid(FileBaseName(),19,2),MID(FileBaseName(),21,2)) as BaseDataFileDate

from [$(vPathData)SIGNREP-DL7592*.txt];

BaseDataFilenames:

NoConcatenate Load *

Resident BaseDataFilenames_Temp

Where BaseDataFileDate > '$(vMaxQVDDate)'

AND Len(FileNameA)>1;

DROP Table BaseDataFilenames_Temp;

Trace Drop Table: BaseDataFilenames_Temp;

Cheers

Kris

Not applicable
Author

Looks like you want BaseDataFilenames_Temp to have the actual filenames from all the files in the folder.  Is that right ?

Not applicable
Author

Correct that is exactly what we are after

Not applicable
Author

Someone may correct me but I don't think it works that way.

Assuming that your filenames are like this: "SIGNREP-DL759220110501.txt"

then you can populate BaseDataFilenames_Temp like this.

FOR EACH sFileName IN FILELIST ('$(vPathData)' & 'SIGNREP-DL7592*.*')

let cur_file = mid('$(sFileName)', index('$(sFileName)', '\',-1)+1);

TRACE $(cur_file);

Data:

LOAD '$(cur_file)' as FileNameA

,MakeDate('20'&MID('$(cur_file)',17,2), Mid('$(cur_file)',19,2), MID('$(cur_file)',21,2)) as BaseDataFileDate

AUTOGENERATE 1;

NEXT sFileName