topic Re: Consecutive data records in QlikView

Consecutive data records

Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have 2 queries highlighted in bold.

Below is the attached data:

1. Firstly I want to add an extra column say Count- which calculates the consecutive number of failures else write 1

ID	Host	Time	Status	Count---Not in data
457	abc	15/04/2016 1:47:19 AM	Success	1
458	abc	15/04/2016 1:47:25 AM	Failure	1
459	abc	15/04/2016 1:47:28 AM	Success	1
460	abc	15/04/2016 2:19:12 AM	Success	1
461	abc	15/04/2016 3:25:07 AM	Success	1
462	abc	15/04/2016 7:01:43 AM	Failure	1
463	abc	15/04/2016 7:01:43 AM	Failure	2
464	abc	15/04/2016 7:01:43 AM	Failure	3
465	abc	15/04/2016 7:01:43 AM	Failure	4
466	abc	15/04/2016 3:12:03 PM	Failure	5
467	abc	15/04/2016 3:12:03 PM	Failure	6
468	abc	16/04/2016 1:13:20 AM	Success	1
469	abc	16/04/2016 1:13:59 AM	Success	1
470	abc	16/04/2016 7:00:24 AM	Failure	1
471	abc	16/04/2016 7:00:24 AM	Failure	2
472	abc	16/04/2016 7:00:26 AM	Failure	3
473	abc	16/04/2016 7:01:20 AM	Failure	4
474	abc	16/04/2016 7:01:20 AM	Failure	5
475	abc	16/04/2016 7:01:21 AM	Failure	6
476	abc	16/04/2016 5:51:15 PM	Failure	7
477	abc	16/04/2016 5:51:15 PM	Failure	8
478	abc	16/04/2016 7:05:46 PM	Success	1
479	abc	16/04/2016 9:11:40 PM	Success	1
480	abc	16/04/2016 9:18:48 PM	Success	1
481	abc	16/04/2016 10:23:32 PM	Success	1
482	abc	17/04/2016 12:00:25 AM	Failure	1
483	abc	17/04/2016 12:00:25 AM	Failure	2
484	abc	17/04/2016 12:00:25 AM	Failure	3
485	abc	17/04/2016 12:00:25 AM	Failure	4
486	abc	17/04/2016 9:09:47 AM	Success	1
487	abc	17/04/2016 11:08:55 AM	Success	1
488	abc	18/04/2016 2:27:24 AM	Success	1
489	abc	18/04/2016 2:33:15 AM	Success	1
490	abc	18/04/2016 3:52:05 AM	Success	1

2. My second Requirement is to get the count of consecutive failures and start time and end time of consecutive failures

So, according to my requirement -- the resultant should be as below:

Host	# of Consecutive Failure	Start Time	End Time
abc	6	15/04/2016 7:01:43 AM	15/04/2016 3:12:03 PM
abc	8	16/04/2016 7:00:24 AM	16/04/2016 5:51:15 PM
abc	4	17/04/2016 12:00:25 AM	17/04/2016 12:00:25 AM

Pls help.

Regards,

Anjali Gupta

Re: Consecutive data records

sunny_talwar — Wed, 20 Apr 2016 15:49:33 GMT

First part of the requirement:

Table:

LOAD ID,

Host,

Time,

Status

FROM

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

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

FinalTable:

LOAD *,

If(Status = 'Failure',

If(Status = Peek('Status'), RangeSum(Peek('Count'), 1), 1), 1) as Count,

If(Status = 'Failure', If(Status <> Peek('Status'), 1),

If(Status <> Peek('Status'), 2)) as Flag

Resident Table

Order By Time;

DROP Table Table;

Re: Consecutive data records

sunny_talwar — Wed, 20 Apr 2016 16:04:19 GMT

1st and 2nd Requirement

Table:

LOAD ID,

Host,

Time,

Status

FROM

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

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

FinalTable:

LOAD *,

If(Status = 'Failure',

If(Status = Peek('Status'), RangeSum(Peek('Count'), 1), 1), 1) as Count,

If(Status <> Peek('Status'), RangeSum(Peek('Flag'), 1), Alt(Peek('Flag'), 1)) as Flag

Resident Table

Order By Time;

DROP Table Table;

Re: Consecutive data records

Wed, 20 Apr 2016 17:38:22 GMT

Hi Sunny,

Thanks for your help.

If you can attach the qvw for me to do the 2nd Requirement it would me great.

Also, for the 2nd requirement I don't want the failures with # of Consecutive Failures = 1.

I will restrict it in the chart.

Regards,

Anjali Gupta

Re: Consecutive data records

sunny_talwar — Wed, 20 Apr 2016 17:41:38 GMT

It was attached above, but reattaching it after making the asked modifications

Re: Consecutive data records

Wed, 20 Apr 2016 17:46:29 GMT

Thanks a lot sunny.

You are a genious...

Regards,

Anjali Gupta