topic Re: Value occures over a time span in QlikView

Value occures over a time span

Thu, 20 Dec 2012 15:42:59 GMT

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

Re: Value occures over a time span

swuehl — Thu, 20 Dec 2012 16:10:23 GMT

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.

Re: Value occures over a time span

Fri, 21 Dec 2012 08:55:55 GMT

Thanks for quick answer, it works perfect.

/Peter