Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Start / end date and flags

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

10 Replies

Re: Start / end date and flags

Looking to get something like this?

Capture.PNG

Not applicable

Re: Start / end date and flags

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.

MVP
MVP

Re: Start / end date and flags

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


MVP
MVP

Re: Start / end date and flags

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

Not applicable

Re: Start / end date and flags

Thank you for this interesting solution.

I discover the power of the peek function.

Not applicable

Re: Start / end date and flags

Hello John,

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

MVP
MVP

Re: Start / end date and flags

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.

Not applicable

Re: Start / end date and flags

Yes please post it, I am interested.

Thank you in advance.

MVP
MVP

Re: Start / end date and flags

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