6 Replies Latest reply: Oct 23, 2015 3:51 PM by Kevin Bertsch RSS

    Proper syntax for FOR..NEXT loop and LOAD statement

      We break our sales down into five stages, 00, 20, 40, 60, 80. I have to find out how long it takes between each stage, and set the background colour in red for that one. I have a table created in my script, OM, that includes

       

      OM:

      Select

      Company,

      OPID, <- Opp ID, a unique 16-char field that identifies each opp'y

      Stage,

      OnDate,

      (other fields)

      from database

      ORDER BY

      Company, OPID, OnDate

       

      Now, each company can have many opportunities, and most opportunities do NOT include all the five stages, so I wanted to do the calculation in the script by first ensuring that two adjacent records are for the same company and the same OPID. If so, I subtract one from the other. I have immediately under my ORDER BY statement above

       

      UNQUALIFY OPID;

       

      For i = 0 to NoOfRows('OM')-1

           IF peek('COMPANY',i,'OM') = peek('COMPANY',i+1,'OM') THEN

                 IF peek('OPID',i,'OM') = peek('OPID',i+1,'OM') THEN

                StDiff: //Note: I tried with and without this table label. Same error each time

                LOAD

                OPID, // loaded to link files

                peek('OnDate',i+1,'OM')-peek('OnDate',i,'OM') as StageDiff,

                *;

            ENDIF

      ENDIF

      Next i

       

      This completes without error, but the field "StageDiff" is nowhere to be found.

       

      Can I use an IF THEN ELSE within a FOR NEXT loop?

      Can I have a LOAD statement inside an IF THEN ELSE?

       

      Any other ideas on how to do this correctly?

        • Re: Proper syntax for FOR..NEXT loop and LOAD statement
          Sunny Talwar

          I don't see why if then won't work within FOR loop. but to get the syntax right, I would prefer having the script so that I can test run it. I know some other experts may be able to help you just by looking at the code, but if possible would you be able to share a app with some data and the above mentioned code where it doesn't seem to be working?

           

          Best,

          Sunny

          • Re: Proper syntax for FOR..NEXT loop and LOAD statement
            Imran Khan

            Look for below example;

             

            Sub LoadTableNames
            SQLTableList:
            LOAD
            Name AS TableNames;
            SELECT Name FROM ContosoRetailDW.sys.tables;

            End Sub;

            // ======================================================//

            Call LoadTableNames;

            // ======================================================//

            Let vTableCount = NoOfRows(‘SQLTableList’);

            // ======================================================//

            Sub LoadTableData

            For i = 0 To $(vTableCount) – 1
            LET vMyTableName = Peek(‘TableNames’, $(i), ‘SQLTableList’);
            $(vMyTableName):
            SQL SELECT * FROM ContosoRetailDW.dbo.$(vMyTableName);
            STORE $(vMyTableName) INTO $(Directory)$(vMyTableName).QVD;
            DROP Table $(vMyTableName);
            Next i

            End Sub;

            // ==========================================================================================//

            Call LoadTableData;

            // ==========================================================================================//

             

              • Re: Proper syntax for FOR..NEXT loop and LOAD statement

                thanks, Imran, but I think my issue has to do with the fact that my LOAD statement is nested in two IF statements,

                which I don't see in your example above.

                 

                As I said, my script completes without error, but "StageDiff" does not get added to either the existing OM table, nor does it get added to the StDiff: table I tried to create (and as I noted, regardless of whether that table label is present or not, the script completes but the data disappears).

              • Re: Proper syntax for FOR..NEXT loop and LOAD statement

                I made some changes in my code, and found the following.

                First, the new code (contains some lines so I could watch the debugger)

                spcode.PNG

                In a limited load using the debugger, I noted that the first two lines of the OM file had the same company and the same OPID, so I would have expected that after the statement

                     if peek('OPID',i,'OM')=peek('OPID",i+1,'OM)

                it would try to execute the LOAD statement.However, the debugger just showed it skipping directly to the ENDIF's. Here's a screen grab from the debugger, showing COMP1=COMP2 AND OP1=OP2.

                spcode2.png

                 

                But the "THEN" statement (i.e. the LOAD block) is not executing. I can't figure out why, unless this is not supported, and the non-support is not documented.

                • Re: Proper syntax for FOR..NEXT loop and LOAD statement
                  Christian Wolf

                  Try:

                   

                  OM:

                  Load

                  *,

                  if(Company=Previous(Company) and OPID=Previous(OPID),OnDate-Previous(OnDate)) as StageDiff

                  ;

                  Select

                  Company,

                  OPID,

                  Stage,

                  OnDate,

                  (other fields)

                  from database

                  ORDER BY

                  Company, OPID, OnDate