4 Replies Latest reply: Nov 7, 2013 7:23 AM by Friedrich Hofmann RSS

    Building a FOR...NEXT loop based on Row_nrs.

    Friedrich Hofmann



      I have a preliminary question:

      - I want to build a LOOP over all the lines of a table I have just loaded.

      - In the LOAD statement, I load >>> RowNo() as Line <<<.

      - It seems, however that I cannot define variables for the lower and upper bounds (based on that field) in the script.

         I tried several times now - I can define those variables on the GUI no problem, but not in the script.

      <=> I need those bounds in some form (variables or Fields) so I can set up my LOOP.

      Can someone help me there, please?


      Thanks a lot!

      Best regards,



        • Re: Building a FOR...NEXT loop based on Row_nrs.
          Gysbert Wassenaar

          for i = 1 to noofrows('TableName')


          //     do stuff



          • Re: Building a FOR...NEXT loop based on Row_nrs.
            Jonathan Dienst



            The lower bound will be 0 (for peek) or 1 for row number. The function NoOfRows('TableName') will in script return the number of rows loaded, so the upper bound would by that value (or value - 1 for peek).


            Let zSize = NoOfRows('TableName');

            For zi = 0 To zSize - 1

                 Let zValue = Peek('MyField', zi, 'TableName');

                 ... do something here ...


            Set zi = ;

            Set zSize = ;

            Set zValue = ;


            (Or have I misunderstood you?)



              • Re: Building a FOR...NEXT loop based on Row_nrs.
                Friedrich Hofmann

                Hi Jonathan,


                NoOfRows() is a function I have used in another scenario already. I can well use that.

                I merely think you have misunderstood me insofar as your code seems to be for something more complicated than I want - in the end, it is going to be quite complicated, but not at this stage. I will explain the entire scope of this task so you can better understand just what I need:


                - I have, with some help from swuehl, developed a code that works fine for ONE employee. It involves

                  - a MAPPING LOAD

                  - several LOAD statements (from different tables, some of them concatenated)

                  - a "blowup" of a table (to one_record_per_day)

                - The next step is to execute that code in a LOOP for every employee we have

                => I have already developed a LOAD from the masterdata table that gives me 1 record per employee

                => Now I want to build the LOOP over that table

                <=> However, that LOOP would be very large and probably quite hard to oversee. I wonder whether it might not be a better idea to

                - develop a subroutine (well, that would just be the code I already have working on ONE employee_nr)

                - build a LOOP over all the lines of that masterdata_table and in that LOOP

                  - define a variable with the emp_id I find in that line

                  - call the subroutine and pass that id_variable as a parameter to the subroutine.


                I have very limited experience with subroutines, but I think there's nothing much to it, it's basically just a script.

                Do you know of any pitfalls I have to keep in mind (like any customary script_commands that will NOT work inside a subroutine)?


                Thanks a lot!

                Best regards,




                I think that's better.

                Is there

                  • Re: Building a FOR...NEXT loop based on Row_nrs.
                    Friedrich Hofmann



                    I thought I was now underway - I would first develop the code to - still for ONE employee - take the data RESIDENT from my base_load and via SQL from the second database_table - but now I am stopped by a thing that should actually be very simple - the way big projects often fail because of very little things...

                    => I still cannot seem to define (or rather, I cannot check that I have successfully defined) a variable in the script:

                         - I have a RESIDENT LOAD now that  loads just one record, for one employee

                         - In the second table, I cannot count on the row_nrs being the same, so I have to filter that second LOAD using
                           the emp_id.
                           => To that end I have to somehow put the emp_id (which is in a field in that one loaded record) in a variable.

                    <=> Using the usual syntax with LET, I get no error, but I cannot see that variable on GUI-level - and I don't trust in its existence if I cannot see it...

                    Can you tell me what I am doing wrong there?

                    Thanks a lot!

                    Best regards,




                    P.S.: OK, I have it - it's awesome how reading up in the help_file often helps - sometimes combined with some guessing at what could be the right keyword to look for - in this case, the PEEK() function helped: In this scenario, I just loaded only one record, so I don't have to specify anything else, just the field_name.