topic Conditional counter in QlikView

Conditional counter

galileumax — Fri, 14 Sep 2018 12:52:49 GMT

Hello to All,

I need your help to solve the following problem:

Input table is as follows:

ID	Name	Date	Stop
1	Paperone	03/01/2018 18:35
2	Paperone	20/01/2018 09:44
3	Paperone	23/04/2018 22:48	Stop
4	Paperone	24/04/2018 11:57
5	Paperone	29/04/2018 16:28
6	Paperone	29/04/2018 16:30	Stop
7	Paperone	30/04/2018 23:18
8	Paperone	02/05/2018 14:28
9	Paperone	24/07/2018 14:33
10	Pippo	04/01/2018 15:51
11	Pippo	02/08/2018 13:58	Stop
12	Pippo	05/08/2018 11:00
13	Pippo	05/08/2018 11:01
14	Pippo	06/08/2018 23:26
15	Topolino	02/01/2018 13:04
16	Topolino	02/01/2018 15:46
17	Topolino	10/01/2018 09:40	Stop
18	Topolino	11/01/2018 10:39
19	Topolino	12/01/2018 10:39
20	Topolino	15/01/2018 16:17
21	Topolino	20/01/2018 18:10

When in correspondence of a name there is a "stop" the count begins

and if the difference between the date of the successive records for the same "Name" is less than 3 then "OK"

Perhaps the desired output is clearer:

ID	Name	Date	Stop	Delta	Output
1	Paperone	03/01/2018 18:35
2	Paperone	20/01/2018 09:44
3	Paperone	23/04/2018 22:48	Stop
4	Paperone	24/04/2018 11:57		0,5	Ok
5	Paperone	29/04/2018 16:28		5,7
6	Paperone	29/04/2018 16:30	Stop	5,7
7	Paperone	30/04/2018 23:18		1,3	Ok
8	Paperone	02/05/2018 14:28		2,9	Ok
9	Paperone	24/07/2018 14:33		85,9
10	Pippo	04/01/2018 15:51
11	Pippo	02/08/2018 13:58	Stop
12	Pippo	05/08/2018 11:00		2,9	Ok
13	Pippo	05/08/2018 11:01		2,9	Ok
14	Pippo	06/08/2018 23:26		4,4
15	Topolino	02/01/2018 13:04
16	Topolino	02/01/2018 15:46
17	Topolino	10/01/2018 09:40	Stop
18	Topolino	11/01/2018 10:39		1,0	Ok
19	Topolino	12/01/2018 10:39		2,0	Ok
20	Topolino	15/01/2018 16:17		5,3
21	Topolino	20/01/2018 18:10		10,4

Thanks in advance

Massimo

Re: Conditional counter

rubenmarin — Fri, 14 Sep 2018 13:02:07 GMT

Hi Massimo, maybe with:

LOAD Stop,

If(Stop<>'Stop', Peek(Delta)+Date-Peek(Date)) as Delta,

If(Stop='Stop', '',

If(Peek(Stop)='Stop',

If(Delta<3, 'Ok',''),

If(Delta<Peek(Delta)+3, 'Ok','')

)) as Output

Re: Conditional counter

MindaugasBacius — Fri, 14 Sep 2018 13:39:20 GMT

Try this script:

tmp:

LOAD ID,

Name,

Date#(Date, 'DD/MM/YYYY hh:mm') as Date,

Stop

FROM

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

tmp2:

LOAD *

Resident tmp

Order by Name asc, Date asc

;

DROP Table tmp;

NoConcatenate

tmp:

LOAD ID

,Name

,Date

,If(Peek(Name) = Name and Len(Trim(Stop)) = 0, Peek(Stop), Stop) as Stop

Resident tmp2;

DROP Table tmp2;

NoConcatenate

tmp2:

LOAD *

,If(Peek(Stop) = 'Stop' and Peek(Name) = Name,

If(Date - Peek(Date) < 3, 'OK', Null())) as Trigger

,If(Peek(Stop) = 'Stop' and Peek(Name) = Name,

Date - Peek(Date), Null()) as Delta

Resident tmp;

DROP Table tmp;

The results looks promising:

Also, I could find out how you have managed to get Deltas like this: