2 Replies Latest reply: Jan 9, 2013 10:24 AM by Mindy Kay RSS

    How to code a LOOP?

      I’m looking for help with coding a LOOP which I’ve never done before.  Do I do this in the SELECT statement or the LOAD statement?  Basically, what I want to do is create a StartDttm for each log entry associated with a Job Report.  As is the timelog table only stores the number of hours that a particular entry took.  Their dates would be derived from the Job Report StartDttm with the first entry's StartDttm equaling the Job's StartDttm.  The remaining Log Entries StartDttms would be derived by adding the HrsDur of the previous ones to the Job Report StartDttm or possibly from the previous derived StartDttm.

       

      Logic:

      SELECT

         RptID,

         RptStartDttm,

         RptEndDttm,

         LogID,

         TimeCode,

         LogSeq#,

         HrsDur,

         CASE WHEN LogSeq# = 1 THEN RptStartDttm               // -- from here down is just my thoughts on the logic

                       ELSE

                                 DO WHILE  …… ??? ……

                                    LogSeq# = 2 THEN RptStartDttm + LogSeq#1 HrsDur

                                    LogSeq# = 3 THEN RptStartDttm + LogSeq#1 HrsDur + LogSeq#2 HrsDur

                                    LogSeq# = 4 THEN RptStartDttm + LogSeq#1 HrsDur + LogSeq#2 HrsDur + LogSeq#3 HrsDur

                                   etc.  until last LogSeq# for RptID

                        --- (OR) ---

                                   LogSeq# = 2 THEN RptStartDttm + LogSeq#1 HrsDur

                                   LogSeq# = 3 THEN LogSeq#2 OpsStartDttm + LogSeq#2 HrsDur

                                   LogSeq# = 4 THEN LogSeq#3 OpsStartDttm + LogSeq#3 HrsDur

                                  etc. until last LogSeq# for RptID

         END AS OpsStartDttm

      FROM

         JobRpt,

         TimeLog

      WHERE

         JobRpt.RptID = TimeLog.RptID

       

       

      SQL Tables:

       

      JobRpt

      RptID       RptStartDttm                     RptEndDttm

      R1           01/13/2013 06:00                01/14/2013 06:00

      R2           01/14/2013 06:00                01/15/2013 06:00

       

      TimeLog

      LogID     RptID       HrsDur           TimeCode           LogSeq#

      L1A           R1            9                MIRU               1             

      L2B        R1          1                BHA                 2             

      L3C        R1          4                DR                   3             

      L4D        R1          2                SVY                 4             

       

      L1AF      R2          5                BHA                 1             

      L21G      R2          6                DR                    2             

      L31D      R2          7                RR                    3             

       

       

      QlikView Table:

      Operations

      RptID       RptStartDttm             RptEndDttm              LogID     TimeCode    LogSeq#       HrsDur       OpsStartDttm (derived field)     

      R1            01/13/2013 06:00        01/14/2013 06:00       L1A         MIRU            1                   9                01/13/2013 06:00

      R1            01/13/2013 06:00        01/14/2013 06:00       L2B         BHA              2                  1                01/13/2013 15:00

      R1            01/13/2013 06:00        01/14/2013 06:00       L3C         DR                3                  4                01/13/2013 16:00

      R1            01/13/2013 06:00        01/14/2013 06:00       L4D         SVY              4                  2                01/13/2013 20:00

       

      R2            01/14/2013 06:00        01/15/2013 06:00        L1AF      BHA              1                  5                01/14/2013 06:00

      R2            01/14/2013 06:00        01/15/2013 06:00        L21G      DR                2                  6                01/14/2013 11:00

      R2            01/14/2013 06:00        01/15/2013 06:00        L31D      RR                3                  7                01/14/2013 17:00

        • Re: How to code a LOOP?
          Gysbert Wassenaar

          Something like this:

           

          JobRpt:

          load RptID,

          timestamp#(RptStartDttm,'MM/DD/YYYY hh:mm') as RptStartDttm,

          timestamp#(RptEndDttm,'MM/DD/YYYY hh:mm') as RptEndDttm

          inline [

          RptID,RptStartDttm,RptEndDttm

          R1,01/13/2013 06:00,01/14/2013 06:00

          R2,01/14/2013 06:00,01/15/2013 06:00

          ];

           

          join load * inline [

          LogID,RptID,HrsDur,TimeCode,LogSeq#

          L1A,R1,9,MIRU,1            

          L2B,R1,1,BHA,2            

          L3C,R1,4,DR,3            

          L4D,R1,2,SVY,4             

          L1AF,R2,5,BHA,1            

          L21G,R2,6,DR,2            

          L31D,R2,7,RR,3          

          ];

           

          Timelog:

          load LogID,RptID,HrsDur,TimeCode,LogSeq#,RptStartDttm,RptEndDttm,

          timestamp(if(RptID=Previous(RptID), peek('OpsStartDttm') + peek('HrsDur')/24, RptStartDttm),'MM/DD/YYYY hh:mm') as OpsStartDttm

          Resident JobRpt order by RptID,LogSeq#;

           

          drop table JobRpt;

            • Re: How to code a LOOP?

              Thanks a lot for your help.  I don't really understand how the code works so I'll have to study it more when I've got the time. I like that it's just one line that does the trick. However, I did have to change Timestamp#() to Date() in my code for it to work and display properly.