topic Error with FOR loop in QlikView

Error with FOR loop

Wed, 15 Jun 2011 10:18:27 GMT

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

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

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

Error wording is:

Script line error:

Error with FOR loop

Wed, 15 Jun 2011 10:59:06 GMT

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

];

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

************* 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 ---

Error with FOR loop

Wed, 15 Jun 2011 11:18:19 GMT

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

Error with FOR loop

Wed, 15 Jun 2011 11:22:12 GMT

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.

Error with FOR loop

Wed, 15 Jun 2011 11:25:48 GMT

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

Error with FOR loop

Wed, 15 Jun 2011 12:38:17 GMT

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

Error with FOR loop

Wed, 15 Jun 2011 12:51:26 GMT

Correct that is exactly what we are after

Error with FOR loop

Wed, 15 Jun 2011 13:56:34 GMT

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