Value occures over a time span

[#1]

Hi!

Need some help to solve this problem.

I have a table with ID, Date and Value, as below and I want to count as True every non-zero value if it occure for more than 5 minutes. e g the value 1 occure for more than 5 minutes between 2012-01-10 21:11:16 and 21:17:26 and once again between 21:21:36 and 21:29:20.

ID	Date	Value
34319	2012-01-10  21:09:43	0
34319	2012-01-10  21:11:16	1
34319	2012-01-10  21:12:18	1
34319	2012-01-10  21:15:55	1
34319	2012-01-10  21:17:26	1
34319	2012-01-10  21:18:30	0
34319	2012-01-10  21:21:36	1
34319	2012-01-10  21:23:08	1
34319	2012-01-10  21:24:41	1
34319	2012-01-10  21:29:20	1
34320	2012-01-10  22:08:51	0
34320	2012-01-10  22:11:26	0
34320	2012-01-10  22:14:01	2
34320	2012-01-10  22:15:03	2
34320	2012-01-10  22:18:39	2
34320	2012-01-10  22:20:48	2
34320	2012-01-10  22:23:23	1
34320	2012-01-10 22:25:21	0
34320	2012-01-10 22:26:18	1

/Peter

[swuehl · Accepted solution]

Maybe like this:

TEST:

LOAD *,

    if(num#(interval(Date-BlockStartDate,'mm')) >= 5 and Value,AutoNumber(BlockStartDate)) as OverTimeBlockNr;

LOAD ID,

     Date,

     Value,

     if(peek(Value)=0 and Value, Date, peek(BlockStartDate)) as BlockStartDate 

FROM

[http://community.qlik.com/thread/68926?tstart=0]

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

Then a in the front end, an expression like

=count(distinct OverTimeBlockNr)

will return your result.

[#3]

Thanks for quick answer, it works perfect.

/Peter