6 Replies Latest reply: Mar 13, 2017 3:55 PM by Andrew Moberg RSS

    IterNo with Timestamps

    Andrew Moberg

      Hello

       

      I am trying to figure out a way to populate a load statement with time-stamp values that fall between two time-stamps.

      Here is an example:

      LOAD *

      INLINE[

      LOG,Start,End

      123,02/08/17 20:44,02/09/17 01:52

      ]

       

      I would like to be able to see

      02/08/17 20:44

      02/08/17 20:45

      02/08/17 20:46

      etc

       

      For each minute between the Start and End values.

       

      Please let me know if you need any further info.


      Thanks again

        • Re: IterNo with Timestamps
          Sunny Talwar

          Try this:

           

          LOAD LOG,

            TimeStamp(Start + IterNo()/1440 - 1/1440) as Time

          While Start + IterNo()/1440 - 1/1440 <= End;

          LOAD * INLINE [

          LOG, Start, End

          123, 02/08/17 20:44, 02/09/17 01:52

          ];

            • Re: IterNo with Timestamps
              Andrew Moberg

              This works with my inline load, however if I try to use it on a timestamp that I generate in the load statement is does not work. I would assume it has something to do with formatting?

              Here is how I create the timestamp in the load statement:

              Timestamp#

              (Date(SURGERY_DATE) & ' ' &Time(interval([In Room Time]),'hh:mm'),'YYYY-MM-DD hh:mm') as StartDtTm,

              if([In Room Hour]<= 23 AND [Out Room Hour]>=0,Timestamp#(Date(SURGERY_DATE+1) & ' ' &Time(interval([Out of Room Time]),'hh:mm'),'YYYY-MM-DD hh:mm'),Timestamp#(Date(SURGERY_DATE) & ' ' &Time(interval([Out of Room Time],'hh:mm')),'YYYY-MM-DD hh:mm')) as EndDtTm

               

              Could this be creating an issue with the IterNo function?

            • Re: IterNo with Timestamps
              Massimo Grossi

              X:

              LOAD

                LOG,

                Timestamp#(Start, 'MM/DD/YY hh:mm') as Start,

                Timestamp#(End, 'MM/DD/YY hh:mm') as End

              INLINE [

              LOG,Start,End

              123,02/08/17 20:44,02/09/17 01:52

              124,02/08/17 20:45,02/09/17 01:53

              ]

              ;

               

              load

                LOG,

                Timestamp(Start + (IterNo() - 1) / (24*60)) as NewField

              Resident X

              While (Start + (IterNo() - 1) / (24*60*60)) <= End;

              • Re: IterNo with Timestamps
                Digvijay Singh

                Source:

                 

                 

                Load LOG,

                    Timestamp(Timestamp#(Start,'MM/DD/YY hh:mm'),'MM/DD/YY hh:mm') as Start,

                    Timestamp(Timestamp#(End,'MM/DD/YY hh:mm'),'MM/DD/YY hh:mm') as End;

                  

                LOAD * INLINE [

                LOG,Start,End

                123,02/08/17 20:44,02/09/17 01:52

                ];

                 

                Left Join ( Source)

                    Load LOG,

                  Start,

                  End,

                  Timestamp(Start + (Iterno()-1)/(24*60),'MM/DD/YY hh:mm')  as minuteWiseTime

                resident Source

                While Start + Iterno()/(24*60) < End;