2 Replies Latest reply: Jan 27, 2017 4:41 AM by Alexander Müller RSS

    Change Data format

    Alexander Müller

      Hello,

       

      I have this data format:

       

      KeyTimeStart

      End

      1234504.01.2017 13:09:4602.01.2017 23:30:00
      1234505.01.2017 12:11:1704.01.2017 16:15:00
      1234506.01.2017 08:03:1305.01.2017 19:15:00
      1234510.01.2017 10:04:2409.01.2017 05:45:00
      1234510.01.2017 10:04:4809.01.2017 08:15:0009.01.2017 15:45:00
      1234510.01.2017 10:05:0709.01.2017 19:00:00
      1234516.01.2017 08:28:2112.01.2017 05:45:00
      1234516.01.2017 08:51:2813.01.2017 18:00:0016.01.2017 05:45:00
      1234519.01.2017 08:28:2416.01.2017 19:15:00

       

      I need the Table like this:

       

      KeyTimeStartEnd
      1234504.01.2017 13:09:4602.01.2017 23:30:00 04.01.2017 16:15:00
      1234505.01.2017 12:11:17
      1234506.01.2017 08:03:1305.01.2017 19:15:00 09.01.2017 05:45:00
      1234510.01.2017 10:04:24
      1234510.01.2017 10:04:4809.01.2017 08:15:0009.01.2017 15:45:00
      1234510.01.2017 10:05:0709.01.2017 19:00:0012.01.2017 05:45:00
      1234516.01.2017 08:28:21
      1234516.01.2017 08:51:2813.01.2017 18:00:0016.01.2017 05:45:00
      1234519.01.2017 08:28:2416.01.2017 19:15:00date(today())

       

      So if the field "End" isnull, then it should take the data from the next line from "End".

      And if there is no next line in the field "End" it should take the data from today.

      And grouped by the field "Key".

       

      I hope someone can help me.

       

      Thanks!

        • Re: Change Data format
          Stefan Wühl

          Maybe something like

           

          SET TIMESTAMPFORMAT = 'DD.MM.YYYY hh:mm:ss';

           

          Input:

          LOAD Key,

               Time,

               Start,

               End

          FROM

          [https://community.qlik.com/thread/247757]

          (html, codepage is 1252, embedded labels, table is @1);

           

          Result:

          NoConcatenate

          LOAD Key, Time, Start, If(Start,If(Previous(Key)=Key and Len(Trim(End))=0, Alt(Previous(End),Now(1)), Timestamp(Alt(End,Now(1))))) as End

          Resident Input

          Order By Key, Time desc;

           

          DROP TABLE Input;

           

           

           

          Key Time Start End
          1234504.01.2017 13:09:4602.01.2017 23:30:0004.01.2017 16:15:00
          1234505.01.2017 12:11:17  
          1234506.01.2017 08:03:1305.01.2017 19:15:0009.01.2017 05:45:00
          1234510.01.2017 10:04:24  
          1234510.01.2017 10:04:4809.01.2017 08:15:0009.01.2017 15:45:00
          1234510.01.2017 10:05:0709.01.2017 19:00:0012.01.2017 05:45:00
          1234516.01.2017 08:28:21  
          1234516.01.2017 08:51:2813.01.2017 18:00:0016.01.2017 05:45:00
          1234519.01.2017 08:28:2416.01.2017 19:15:0027.01.2017 10:24:41