10 Replies Latest reply: Dec 12, 2016 5:31 AM by jyp jyp RSS

    Start / end date and flags

    jyp jyp

      Hello,

       

      I have a dataset containing dates and flags.

       

      For each date, the flag tells if a device was on (flag = true) or if the device was off (flag = false)

       

        

      14-10-16 01:06TRUE
      14-10-16 01:21FALSE
      14-10-16 02:51TRUE
      14-10-16 03:06FALSE
      14-10-16 04:36TRUE
      14-10-16 04:51FALSE
      14-10-16 06:21TRUE
      14-10-16 06:36FALSE
      14-10-16 08:06TRUE
      14-10-16 08:21FALSE
      14-10-16 09:51TRUE
      14-10-16 10:06FALSE
      14-10-16 11:36TRUE
      14-10-16 11:51FALSE
      14-10-16 13:21TRUE
      14-10-16 13:36FALSE
      14-10-16 15:06TRUE
      14-10-16 15:21

      FALSE

       

       

      How can I exploit these data to create a chart showing the device usage ?

      E.g : time spent with device in status"on", per hour / day / month.

       

      Is it possible to work with these raw data "as is" ? Or do you think I d' need to transform them ?

      Regards,

       

      Jean-Yves

        • Re: Start / end date and flags
          Sunny Talwar

          Looking to get something like this?

           

          Capture.PNG

          • Re: Start / end date and flags
            jyp jyp

            Hello Sunny,

             

            Yes for instance. Let's start with that.

             

            And another idea would be to show the time it was up, and the time is was down.

            • Re: Start / end date and flags
              Massimo Grossi

              I think you can calculate for every row the time and the status using the previous row

              with this script

               

              T:

              LOAD

                Timestamp(Timestamp#(@1, 'DD-MM-YY hh:mm')) as DateTime,

                @2 as Status

              FROM

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

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

               

              T2:

              load

                *,

                Interval(if(peek('DateTime'), DateTime - Peek('DateTime'))) as Interval,

                Peek('Status') as IntervalStatus

              Resident T

              Order By DateTime;

              DROP Table T;

               

               

              The chart on the right displays the time by status (on, off)

              1.png

               

              If you have many dates, I think you should split the records crossing midnight

               

              14/10/2016 23:00     OFF

              14/10/2016 23:15     ON

              15/10/2016 01:15     OFF

               

              14/10/2016 23:00    

              14/10/2016 23:15     00:15     OFF    

              14/10/2016 24:00     00:45     ON

              15/10/2016 01:15     01:15     ON


              • Re: Start / end date and flags
                John Witherspoon

                I'd transform the data.

                 

                I've broken the data down by hour here, as I think you wanted to be able to see, by hour, how much the machine was in use. I changed one of your timestamps to be exactly 8:00 to make sure I didn't introduce a bug in the way I was splitting the hours apart. I don't guarantee bug free, of course, but it seems to work. I've supplied a couple visualizations of the machine usage, but you should be able to build whatever sort of chart you find most useful. In a real application, I'd build a master calendar linking to the hour field here that had Date, Month, Year, that sort of thing. But we only have one day of data here,.

                Capture.PNG

                  • Re: Start / end date and flags
                    jyp jyp

                    Hello John,

                     

                    So you transformed the data manually before loading it in Qlikview ?

                      • Re: Start / end date and flags
                        John Witherspoon

                        No. I transformed the data using QlikView script. If you look at the script, the first load of the Data table is the equivalent of your raw, non-transformed data. After that, I concatenate hour boundaries onto that table, and then I load new table Data2, which is the transformed data. Then I drop Data, as we no longer need the non-transformed data. The only other bit is at the top of the script, I associate colors with TRUE and FALSE. This has nothing to do with solving the basic problem, it's just something I often do when I want the colors in multiple charts to match and to be associated with field values.

                         

                        If you can't see the script because you're using Personal Edition, I can post it.

                          • Re: Start / end date and flags
                            jyp jyp

                            Yes please post it, I am interested.

                            Thank you in advance.

                              • Re: Start / end date and flags
                                John Witherspoon

                                Here's the script:

                                 

                                Flags:
                                LOAD *
                                ,rgb(R,G,B) as Color
                                INLINE [
                                Flag,R,G,B
                                TRUE,38,164,38
                                FALSE,220,220,220
                                ]
                                ;

                                Data:
                                LOAD * INLINE [
                                Start, Flag
                                14-10-16 01:06, TRUE
                                14-10-16 01:21, FALSE
                                14-10-16 02:51, TRUE
                                14-10-16 03:06, FALSE
                                14-10-16 04:36, TRUE
                                14-10-16 04:51, FALSE
                                14-10-16 06:21, TRUE
                                14-10-16 06:36, FALSE
                                14-10-16 08:00, TRUE
                                14-10-16 08:21, FALSE
                                14-10-16 09:51, TRUE
                                14-10-16 10:06, FALSE
                                14-10-16 11:36, TRUE
                                14-10-16 11:51, FALSE
                                14-10-16 13:21, TRUE
                                14-10-16 13:36, FALSE
                                14-10-16 15:06, TRUE
                                14-10-16 15:21, FALSE
                                ]
                                ;
                                CONCATENATE (Data)
                                LOAD *
                                WHERE not exists(Start)
                                ;
                                LOAD makedate(2016,10,14)+div(recno()-1,24)+maketime(mod(recno()-1,24)) as Start
                                AUTOGENERATE 25
                                ;

                                Data2:
                                NOCONCATENATE
                                LOAD *
                                ,interval(End-Start) as Duration
                                ,floor(Start,maketime(1)) as Hour
                                ;
                                LOAD
                                previous(Start) as Start
                                ,Start as End
                                ,if(len(previous(Flag)),previous(Flag),if(len(peek(Flag)),peek(Flag),'FALSE')) as Flag
                                RESIDENT Data
                                WHERE previous(Start)
                                ORDER BY Start
                                ;

                                DROP TABLE
                                Data;

                                 

                                Chart 1:


                                Dimension 1 = Hour
                                Dimension 2 = Flag
                                Expression  = sum(Duration)
                                Background Color = Color

                                 

                                Chart 2:

                                 

                                Dimension 1 = ='Total'
                                Dimension 2 = Start
                                Expression  = sum(Duration)
                                Background Color = Color