5 Replies Latest reply: Jun 18, 2013 8:48 AM by Yang Yang RSS

    Use For Loop to process through every row of a table

    Yang Yang

      Hi,

      I am trying to use for loop to go through every single row of a table and do some operations. However, I can not manage to read each row successfully. Can someone have a look at the attached QVW and tell me why table "TestReplicate" can only replicate one row of the original table "IncidentTemp"? Many thanks in advance.

       

      Regards,

      Yang

        • Re: Use For Loop to process through every row of a table

          Hi

           

          Change this code:

          TestReplicate:

           

          LOAD * INLINE [
          vrid,     vIDate
          $(vRecordID), $(vIncidentDate)
          ]
          ;

           

          To This

          LOAD $(vRecordID) AS vrid

          ,$(vIncidentDate AS vlDate

          Autogenerate(1);

           

           

          That should do the trick

            • Re: Use For Loop to process through every row of a table

              Sorry, went a bit quick then, but this works, notice the different use of LET & SET, in this case you need to use SET as you don't want the expression to be immediately evaluated at that time,

               

              IncidentTemp:

               

               

              LOAD * INLINE [
                  RecordID, IncidentDate, Value
                  1, 01/01/2001, 100
                  2, 05/02/2001, 154
                  3, 12/03/2002, 200
                  4, 09/08/2004, 53
              ]
              ;

              Let vNoOfRows = NoOfRows('IncidentTemp');

              FOR i=0 to $(vNoOfRows)-1
               
              SET vRecordID=Peek('RecordID',$(i),'IncidentTemp');
               
              SET vIncidentDate=Peek('IncidentDate',$(i),'IncidentTemp');

              TestReplicate:
              LOAD IF(ISNULL($(vRecordID)),'NULL'&$(i),$(vRecordID)) as vrid
              ,
              IF(ISNULL($(vIncidentDate)),'NULL'&$(i),$(vIncidentDate)) as vlDate AutoGenerate(1);

              NEXT i;

               

               

               

                • Re: Use For Loop to process through every row of a table
                  Yang Yang

                  Hi Nigel,

                  Thank you very much. It works perfectly.

                   

                  Just out of my curiosity, when I used "let" instead of "set", the data that I will get in TestReplicate table will be:

                  vridvlDate
                  10.00049975
                  20.001249375
                  30.001998002
                  40.000561377

                   

                  Where are those numbers for "vIDate" coming from? They are not timestamps of original "IncidentDate". In addition, it seems "let" works well for ID column and Value column. Only for "IncidentDate" column, it needs to be changed to "set". Do you know why?

                   

                  Thanks.

                   

                  Regards,

                  Yang