Consecutive data records

[#1]

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

[sunny_talwar]

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;

[sunny_talwar · Accepted solution]

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;

[#4]

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

[sunny_talwar]

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

[#6]

Thanks a lot sunny.

You are a genious...

Regards,

Anjali Gupta