Count max number of occurrences of a value on consecutive rows

[#1]

Hi there... I have the table below and I would like to count the maximum number of time the value 'Timeout' appears on consecutive rows.

i.e. on the table below the maximum number of times 'Timeout' appears in consecutive rows is 7

Date	Time	Server	IPAddress	Bytes	Response	Response Time
23/02/2017	04:21:32.2132	Zonal	xxx.xxx.xxx.xxx	32	Success	30
23/02/2017	04:21:35.2135	Zonal	xxx.xxx.xxx.xxx	32	Success	31
23/02/2017	04:21:38.2138	Zonal	xxx.xxx.xxx.xxx	Timeout	Timeout	Timeout
23/02/2017	04:21:41.2141	Zonal	xxx.xxx.xxx.xxx	32	Success	31
23/02/2017	04:21:44.2144	Zonal	xxx.xxx.xxx.xxx	32	Success	30
23/02/2017	04:21:47.2147	Zonal	xxx.xxx.xxx.xxx	Timeout	Timeout	Timeout
23/02/2017	04:21:50.2150	Zonal	xxx.xxx.xxx.xxx	32	Success	30
23/02/2017	04:21:53.2153	Zonal	xxx.xxx.xxx.xxx	Timeout	Timeout	Timeout
23/02/2017	04:21:56.2156	Zonal	xxx.xxx.xxx.xxx	Timeout	Timeout	Timeout
23/02/2017	04:21:59.2159	Zonal	xxx.xxx.xxx.xxx	32	Success	30
23/02/2017	04:22:02.222	Zonal	xxx.xxx.xxx.xxx	32	Success	30
23/02/2017	04:22:05.225	Zonal	xxx.xxx.xxx.xxx	Timeout	Timeout	Timeout
23/02/2017	04:22:08.228	Zonal	xxx.xxx.xxx.xxx	Timeout	Timeout	Timeout
23/02/2017	04:22:11.2211	Zonal	xxx.xxx.xxx.xxx	Timeout	Timeout	Timeout
23/02/2017	04:22:14.2214	Zonal	xxx.xxx.xxx.xxx	Timeout	Timeout	Timeout
23/02/2017	04:22:17.2217	Zonal	xxx.xxx.xxx.xxx	Timeout	Timeout	Timeout
23/02/2017	04:22:20.2220	Zonal	xxx.xxx.xxx.xxx	Timeout	Timeout	Timeout
23/02/2017	04:22:23.2223	Zonal	xxx.xxx.xxx.xxx	Timeout	Timeout	Timeout
23/02/2017	04:22:26.2226	Zonal	xxx.xxx.xxx.xxx	32	Success	30
23/02/2017	04:22:29.2229	Zonal	xxx.xxx.xxx.xxx	32	Success	31
23/02/2017	04:22:32.2232	Zonal	xxx.xxx.xxx.xxx	32	Success	30
23/02/2017	04:22:35.2235	Zonal	xxx.xxx.xxx.xxx	32	Success	30
23/02/2017	04:22:38.2238	Zonal	xxx.xxx.xxx.xxx	32	Success	30
23/02/2017	04:22:41.2241	Zonal	xxx.xxx.xxx.xxx	Timeout	Timeout	Timeout
23/02/2017	04:22:44.2244	Zonal	xxx.xxx.xxx.xxx	32	Success	31
23/02/2017	04:22:47.2247	Zonal	xxx.xxx.xxx.xxx	32	Success	30
23/02/2017	04:22:50.2250	Zonal	xxx.xxx.xxx.xxx	32	Success	30
23/02/2017	04:22:53.2253	Zonal	xxx.xxx.xxx.xxx	32	Success	30
23/02/2017	04:22:56.2256	Zonal	xxx.xxx.xxx.xxx	32	Success	30
23/02/2017	04:22:59.2259	Zonal	xxx.xxx.xxx.xxx	32	Success	30
23/02/2017	04:23:02.232	Zonal	xxx.xxx.xxx.xxx	32	Success	30
23/02/2017	04:23:05.235	Zonal	xxx.xxx.xxx.xxx	32	Success	31
23/02/2017	04:23:08.238	Zonal	xxx.xxx.xxx.xxx	32	Success	30
23/02/2017	04:23:11.2311	Zonal	xxx.xxx.xxx.xxx	Timeout	Timeout	Timeout
23/02/2017	04:23:14.2314	Zonal	xxx.xxx.xxx.xxx	Timeout	Timeout	Timeout
23/02/2017	04:23:17.2317	Zonal	xxx.xxx.xxx.xxx	Timeout	Timeout	Timeout
23/02/2017	04:23:20.2320	Zonal	xxx.xxx.xxx.xxx	Timeout	Timeout	Timeout
23/02/2017	04:23:23.2323	Zonal	xxx.xxx.xxx.xxx	Timeout	Timeout	Timeout
23/02/2017	04:23:26.2326	Zonal	xxx.xxx.xxx.xxx	32	Success	30
23/02/2017	04:23:29.2329	Zonal	xxx.xxx.xxx.xxx	32	Success	30

[lironbaram]

hi after the first reload of the table in the script

you can go like this

will call the first table Data.

///create a counter of timeout in sequences////////////

DataOrdered:

Load *,

         if(Bytes='Timeout' , if (previous(Bytes)='Timeout' ,rangesum(peek('Counter'),1),1),0) As Counter

Resident Data

Order by Date,Time;

drop table Data;

now in the UI , you can use the function max(Counter) to know the longest sequence

[maxgro]

LOAD

  Date,

    Time,

    Server,

    IPAddress,

    Bytes,

    Response,

    [Response Time],

    if(Bytes = 'Timeout' and Peek('Bytes') = 'Timeout', Peek('CountTimeout')+1,

    if(Bytes = 'Timeout', 1, 0)) as CountTimeout

FROM

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

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

[kaushiknsolanki]

Hi,

Check this.

Regards,

Kaushik Solanki

[#5]

Thank you all but so far all of the 3 options above return 1

here is the script with all 3 options on it

ServerPingLogsRaw:

Let Start.ServerPingLogs=Now();

LOAD RowNo() As ServerPing_RowNo,

  Date(@1) As ServerPing_Date,

    Time(@3,'hh:mm:ss.ff') As ServerPing_Time,

    @4 As IPAddress,

    If(@5='Reply','Success','Timeout') As ServerPing_Response,

    @5,

    Keepchar(@8, '0123456789') As ServerPing_Bytes,

    Keepchar(@9, '0123456789') As ServerPing_ResponseTime,

    @10 As ServerPing_TTL

FROM

[\\cdgbipub01\Source Documents\Live\QVApps\Supporting Tasks\06_Other\ServerPingLogs\ServerPingLogs.txt]

(txt, codepage is 1252, no labels, delimiter is ' ', msq);

///create a counter of timeout in sequences////////////

ServerPingLogs:

Load *,

//        if(ServerPing_Response='Timeout' , if (previous(ServerPing_Response)='Timeout' ,rangesum(peek('Counter'),1),1),0) As Counter

// if(@5 = 'Request' and Peek('@5') = 'Request', Peek('CountTimeout')+1,

//    if(@5 = 'Request', 1, 0)) as CountTimeout

  If(Peek(@5)=@5,Peek(Value)+1,1) as Value

Resident ServerPingLogsRaw

Order by ServerPing_Date,ServerPing_Time;

drop table ServerPingLogsRaw;

Max:

Load Max(Value) as Max

Resident ServerPingLogs where Wildmatch(@5,'Request');

[lawrenceiow]

The answers given above are correct but given your reply does not match your example table I suggest you try the following (it still uses the methods mentioned above) and assumes your LOAD statement is correct:

LOAD *
, if(Peek('ServerPing_Response')=ServerPing_Response, Peek('Counter')+1,if(ServerPing_Response= 'Timeout', 1, 0)) as Counter;
LOAD RowNo() As ServerPing_RowNo
, Date(@1) As ServerPing_Date
, Time(@3,'hh:mm:ss.ff') As ServerPing_Time
, @4 As IPAddress
, If(@5='Reply','Success','Timeout') As ServerPing_Response
, @5 as Column5
, Keepchar(@8, '0123456789') As ServerPing_Bytes
, Keepchar(@9, '0123456789') As ServerPing_ResponseTime
, @10 As ServerPing_TTL
FROM
[\\cdgbipub01\Source Documents\Live\QVApps\Supporting Tasks\06_Other\ServerPingLogs\ServerPingLogs.txt]
(txt, codepage is 1252, no labels, delimiter is ' ', msq);

In your document use =Max(Counter) as per suggestions by lironbaram‌ and maxgro‌