Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
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);
Hi,
Check this.
Regards,
Kaushik Solanki
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');
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