Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
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;
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
It was attached above, but reattaching it after making the asked modifications
Thanks a lot sunny.
You are a genious...
Regards,
Anjali Gupta