4 Replies Latest reply: Jul 1, 2015 2:38 AM by Peter Hargreaves RSS

    Find Time Difference from two tables

      Hi

       

      Table1:

       

      KeyReceived_DateTime
      20150309-00029814_110/3/2015 2:28
      20150309-00029814_21/4/2015 16:19
      20150312-00004425_112/3/2015 3:47
      20150312-00004425_21/4/2015 14:20
      20150316-00001394_117/03/2015 02:21
      20150316-00001394_28/4/2015 2:51

      Table2:

       

      KeyReleased_DateTime
      20150309-00029814_11/4/2015 15:07
      20150309-00029814_21/4/2015 17:28
      20150312-00004425_11/4/2015 10:52
      20150312-00004425_21/4/2015 14:50
      20150316-00001394_17/4/2015 16:33
      20150316-00001394_28/4/2015 7:21

       

      Need to find the Interval between Received_DateTime and Released_DateTime based on KEY

       

       

       

      Regards

        • Re: Find Time Difference from two tables
          Prashant Sangle

          Hi,

           

          In script use join to combine both table

          then use Resident load and interval() to calculate time difference

           

          try like

          Tabel1:

          Load *,Key from Table 1;

          join

          Load *,Key from Table2;

           

          Final:

          Load Key,interval(Received_dateTime-Release_datetime,'hh:mm') as TimeDifference

          Resident Table1;

           

          Regards

          • Re: Find Time Difference from two tables
            Tresesco B

            Load the tables as is. Then in the front end try like:

            =Interval(Released_DateTime - Received_DateTime, 'dd:hh:mm:ss')

             

            You can modify the interval output format as per your need, like 'hh' for difference hours, 'mm' for minutes.

            • Re: Find Time Difference from two tables
              Satya Ppaleti

              Hi Kumar,

               

              Try this

              Table1:

               

              load* Inline [

              Key,Received_DateTime

              20150309-00029814_1 ,10/3/2015 2:28

              20150309-00029814_2 ,1/4/2015 16:19

              20150312-00004425_1 ,12/3/2015 3:47

              20150312-00004425_2 ,1/4/2015 14:20

              20150316-00001394_1 ,17/03/2015 02:21

              20150316-00001394_2 ,8/4/2015 2:51

              ];

               

               

              join(Table1)

               

               

              load * inline

              [

              Key,Released_DateTime

              20150309-00029814_1 ,1/4/2015 15:07

              20150309-00029814_2 ,1/4/2015 17:28

              20150312-00004425_1 ,1/4/2015 10:52

              20150312-00004425_2 ,1/4/2015 14:50

              20150316-00001394_1 ,7/4/2015 16:33

              20150316-00001394_2 ,8/4/2015 7:21

              ];

               

               

              load Key,Interval((SubField(Received_DateTime,' ',1) &''&Time(SubField(Released_DateTime,' ',2))) - (SubField(Released_DateTime,' ',1) &''&Time(SubField(Received_DateTime,' ',2))),'dd:hh:mm') as Timedifference

               

               

              Resident Table1;

               

              Thank you,

              Satya Paleti

              • Re: Find Time Difference from two tables
                Peter Hargreaves

                Join the two tables