Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Tags (1)
7 Replies
Not applicable

Error with FOR loop

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

Error with FOR loop

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

Error with FOR loop

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

Error with FOR loop

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

Error with FOR loop

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

Not applicable

Error with FOR loop

Correct that is exactly what we are after

Not applicable

Error with FOR loop

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

Community Browser