4 Replies Latest reply: Jun 1, 2017 10:09 AM by Daniel Kuba RSS

    Time

    Daniel Kuba

      I have three columns. In first I have "item", in second I have time (format is Hour:Minute:Second), third I have date.

      Something like:

       

      A               23:58:56               2017-05-21

      A               23:59:08               2017-05-21

      A               00:02:45               2017-05-22

      A               00:02:59               2017-05-22    

      B               00:03:05               2017-05-22    

      etc.


      And I would like result, where will be new column with result:


      A               23:58:56               2017-05-21         00:00:00

      A               23:59:08               2017-05-21          00:00:12

      A               00:02:45               2017-05-22         00:03:37   

      A               00:02:59               2017-05-22          00:00:14

      B               00:03:05               2017-05-22         00:00:00

      etc.

       

      I need get time duration in seconds between times for each Item, and whit each new item I want start from 0. Anybody have a idea for right formula?

        • Re: Time
          Sunny Talwar

          May be this

           

          Table:

          LOAD *,

            TimeStamp(Date+Time) as TimeStamp;

          LOAD * INLINE [

            Dim, Time, Date

              A, 23:58:56, 2017-05-21

              A, 23:59:08, 2017-05-21

              A, 00:02:45, 2017-05-22

              A, 00:02:59, 2017-05-22   

              B, 00:03:05, 2017-05-22 

          ];

           

          FinalTable:

          LOAD *,

            Interval(If(Dim = Previous(Dim), TimeStamp - Previous(TimeStamp), 0), 'hh:mm:ss') as Diff

          Resident Table

          Order By Dim, TimeStamp;

           

          DROP Table Table;

            • Re: Time
              Daniel Kuba

              It work, thank you very much Sunny.

               

              Only I dont understand this part:

              LOAD * INLINE [

                Dim, Time, Date

                  A, 23:58:56, 2017-05-21

                  A, 23:59:08, 2017-05-21

                  A, 00:02:45, 2017-05-22

                  A, 00:02:59, 2017-05-22 

                  B, 00:03:05, 2017-05-22

              ];

              So I skip it. And maybe it is reason why Qlik wrote me a error, that it dont know Table. I have many more datas. Then I skiped as well row with drop table and it work. I know that isnt good solution, but it work and it is important for me. Thank you again. You are really Qlik wizzard. :-)