7 Replies Latest reply: Jun 15, 2011 9:56 AM by todwith1d RSS

    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

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

            • 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

                • 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

                    • 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

                        • 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 ?

                            • Error with FOR loop

                              Correct that is exactly what we are after

                                • 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