3 Replies Latest reply: Nov 23, 2016 8:49 AM by Manish Kachhia RSS

    Scripting

    mahesh babu

      I have date  fields  like start date and end date in my table

      start date                                  end date

      2016-10-23 00:00:00:00             2016-10-24 23:58:59:00

      2016-11-24 00:00:00:00             2017-01-10 23:59:59:00

      2016-12-01 00:00:00;00            2016-12-31 23:58:59:00

      2016-12-28 00:00:00:00            2017-05-1 00:00:00:00

      2017-01-01 00:00:00:00             2017-08-08 23:58:58:00

       

      i want the  total hours between these date range and i want to this in the script level

      for Example :  start date:2016-10-23 00:00:00:00 and end date: 2016-10-24 23:58:59:00  i want as total hours spent was 48 hours

       

      can any one help me on this

       

       

      Thanks

      Mahesh

        • Re: Scripting
          Manish Kachhia
          Data:
          Load *, DifferenceInTime * 24 as Difference;
          Load
            Interval(TimeStamp#([end date],'YYYY-MM-DD hh:mm:ss:ff') - TimeStamp#([start date],'YYYY-MM-DD hh:mm:ss:ff')) as DifferenceInTime,
            TimeStamp#([start date],'YYYY-MM-DD hh:mm:ss:ff') as [start date],
            TimeStamp#([end date],'YYYY-MM-DD hh:mm:ss:ff') as [end date]
          Inline
          [
            start date,             end date
            2016-10-23 00:00:00:00, 2016-10-24 23:58:59:00
            2016-11-24 00:00:00:00, 2017-01-10 23:59:59:00
            2016-12-01 00:00:00;00, 2016-12-31 23:58:59:00
            2016-12-28 00:00:00:00, 2017-05-1 00:00:00:00
            2017-01-01 00:00:00:00, 2017-08-08 23:58:58:00
          ];
          
            • Re: Scripting
              mahesh babu

              Hi manish

               

              Thank you for fast response .  can you please explain these syntax

              1. Data: 
              2. Load *, DifferenceInTime * 24 as Difference; 
              3. Load 
              4.   Interval(TimeStamp#([end date],'YYYY-MM-DD hh:mm:ss:ff') - TimeStamp#([start date],'YYYY-MM-DD hh:mm:ss:ff')
                • Re: Scripting
                  Manish Kachhia

                  1) I have converted start date and end date into timestamp using TimeStamp# function.

                  Read below blog post to understand difference between TimeStamp and TimeStamp#.

                  QlikView Date fields

                   

                  2) Now you can find the difference between them. In case if we need to show more than 24 hours, use Interval function.

                  This will give you difference in hh:mm:ss format.

                   

                  3) Now multiply the result by 24 which will convert it to hours ...