Start / end date and flags

[#1]

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: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:06	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

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

[sunny_talwar]

Looking to get something like this?

[#3]

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.

[maxgro]

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)

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

[johnw]

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,.

[#6]

Thank you for this interesting solution.

I discover the power of the peek function.

[#7]

Hello John,

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

[johnw]

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.

[#9]

Yes please post it, I am interested.

Thank you in advance.

[johnw]

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