Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

Count max number of occurrences of a value on consecutive rows

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

   

DateTimeServerIPAddressBytesResponseResponse Time
23/02/201704:21:32.2132Zonalxxx.xxx.xxx.xxx32Success30
23/02/201704:21:35.2135Zonalxxx.xxx.xxx.xxx32Success31
23/02/201704:21:38.2138Zonalxxx.xxx.xxx.xxxTimeoutTimeoutTimeout
23/02/201704:21:41.2141Zonalxxx.xxx.xxx.xxx32Success31
23/02/201704:21:44.2144Zonalxxx.xxx.xxx.xxx32Success30
23/02/201704:21:47.2147Zonalxxx.xxx.xxx.xxxTimeoutTimeoutTimeout
23/02/201704:21:50.2150Zonalxxx.xxx.xxx.xxx32Success30
23/02/201704:21:53.2153Zonalxxx.xxx.xxx.xxxTimeoutTimeoutTimeout
23/02/201704:21:56.2156Zonalxxx.xxx.xxx.xxxTimeoutTimeoutTimeout
23/02/201704:21:59.2159Zonalxxx.xxx.xxx.xxx32Success30
23/02/201704:22:02.222Zonalxxx.xxx.xxx.xxx32Success30
23/02/201704:22:05.225Zonalxxx.xxx.xxx.xxxTimeoutTimeoutTimeout
23/02/201704:22:08.228Zonalxxx.xxx.xxx.xxxTimeoutTimeoutTimeout
23/02/201704:22:11.2211Zonalxxx.xxx.xxx.xxxTimeoutTimeoutTimeout
23/02/201704:22:14.2214Zonalxxx.xxx.xxx.xxxTimeoutTimeoutTimeout
23/02/201704:22:17.2217Zonalxxx.xxx.xxx.xxxTimeoutTimeoutTimeout
23/02/201704:22:20.2220Zonalxxx.xxx.xxx.xxxTimeoutTimeoutTimeout
23/02/201704:22:23.2223Zonalxxx.xxx.xxx.xxxTimeoutTimeoutTimeout
23/02/201704:22:26.2226Zonalxxx.xxx.xxx.xxx32Success30
23/02/201704:22:29.2229Zonalxxx.xxx.xxx.xxx32Success31
23/02/201704:22:32.2232Zonalxxx.xxx.xxx.xxx32Success30
23/02/201704:22:35.2235Zonalxxx.xxx.xxx.xxx32Success30
23/02/201704:22:38.2238Zonalxxx.xxx.xxx.xxx32Success30
23/02/201704:22:41.2241Zonalxxx.xxx.xxx.xxxTimeoutTimeoutTimeout
23/02/201704:22:44.2244Zonalxxx.xxx.xxx.xxx32Success31
23/02/201704:22:47.2247Zonalxxx.xxx.xxx.xxx32Success30
23/02/201704:22:50.2250Zonalxxx.xxx.xxx.xxx32Success30
23/02/201704:22:53.2253Zonalxxx.xxx.xxx.xxx32Success30
23/02/201704:22:56.2256Zonalxxx.xxx.xxx.xxx32Success30
23/02/201704:22:59.2259Zonalxxx.xxx.xxx.xxx32Success30
23/02/201704:23:02.232Zonalxxx.xxx.xxx.xxx32Success30
23/02/201704:23:05.235Zonalxxx.xxx.xxx.xxx32Success31
23/02/201704:23:08.238Zonalxxx.xxx.xxx.xxx32Success30
23/02/201704:23:11.2311Zonalxxx.xxx.xxx.xxxTimeoutTimeoutTimeout
23/02/201704:23:14.2314Zonalxxx.xxx.xxx.xxxTimeoutTimeoutTimeout
23/02/201704:23:17.2317Zonalxxx.xxx.xxx.xxxTimeoutTimeoutTimeout
23/02/201704:23:20.2320Zonalxxx.xxx.xxx.xxxTimeoutTimeoutTimeout
23/02/201704:23:23.2323Zonalxxx.xxx.xxx.xxxTimeoutTimeoutTimeout
23/02/201704:23:26.2326Zonalxxx.xxx.xxx.xxx32Success30
23/02/201704:23:29.2329Zonalxxx.xxx.xxx.xxx32Success30
5 Replies
Partner
Partner

Re: Count max number of occurrences of a value on consecutive rows

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

MVP
MVP

Re: Count max number of occurrences of a value on consecutive rows

1.png


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);

Re: Count max number of occurrences of a value on consecutive rows

Hi,

Check this.

Regards,

Kaushik Solanki

Not applicable

Re: Count max number of occurrences of a value on consecutive rows

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
Contributor II

Re: Count max number of occurrences of a value on consecutive rows

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 lironbaramand maxgro