increment field on dimension changes

[#1]

Hi Qlikers,

Table1:

LOAD * Inline [
Date, Employee, Event
01/01/2015, Pierre, A
02/01/2015, Pierre, A
03/01/2015, Pierre, B
04/01/2015, Pierre, C
05/01/2015, Pierre, C
06/01/2015, Pierre, A
01/01/2015, Rachid, A
02/01/2015, Rachid, D
03/01/2015, Rachid, D
04/01/2015, Rachid, D
05/01/2015, Rachid, E
06/01/2015, Rachid, E ];

With this script, what is the best way to create an 'EventGroup' field. 'EventGroup' must change (increment) if 'Event' or 'Employee' has changed. Table1 is sorted by Date, Employee, Event.

The final table should look like this :

Date	Employee	Event	EventGroup
01/01/2015	Pierre	A	1
02/01/2015	Pierre	A	1
03/01/2015	Pierre	B	2
04/01/2015	Pierre	C	3
05/01/2015	Pierre	C	3
06/01/2015	Pierre	A	4
01/01/2015	Rachid	A	5
02/01/2015	Rachid	D	6
03/01/2015	Rachid	D	6
04/01/2015	Rachid	D	6
05/01/2015	Rachid	E	7
06/01/2015	Rachid	E	7

Thanks,

Xavier.

[Gysbert_Wassenaar · Accepted solution]

Maybe like this:

Table1:

LOAD *, if(Previous(Employee&'|'&Event) = Employee&'|'&Event,peek(EventGroup),1+peek(EventGroup)) as EventGroup Inline [
Date, Employee, Event
01/01/2015, Pierre, A
02/01/2015, Pierre, A
03/01/2015, Pierre, B
04/01/2015, Pierre, C
05/01/2015, Pierre, C
06/01/2015, Pierre, A
01/01/2015, Rachid, A
02/01/2015, Rachid, D
03/01/2015, Rachid, D
04/01/2015, Rachid, D
05/01/2015, Rachid, E
06/01/2015, Rachid, E ];

[#3]

Thanks Gysbert, you save me .

---

Nota bene : I had to initialize the first value of 'EventGroup' with the isnull() function.

Table1:
LOAD *,
     if(
     Previous(Employee&'|'&Event) = Employee&'|'&Event,
     peek(EventGroup),
     if(isnull(peek(EventGroup)),1,1+peek(EventGroup))) as EventGroup
Inline [
Date, Employee, Event
01/01/2015, Pierre, A
02/01/2015, Pierre, A
03/01/2015, Pierre, B
04/01/2015, Pierre, C
05/01/2015, Pierre, C
06/01/2015, Pierre, A
01/01/2015, Rachid, A
02/01/2015, Rachid, D
03/01/2015, Rachid, D
04/01/2015, Rachid, D
05/01/2015, Rachid, E
06/01/2015, Rachid, E ];

exit Script;

[Gysbert_Wassenaar]

Ah yes, good point. That's why I usually use rangesum(1,peek(EventGroup)) instead of 1+peek(EventGroup). Rangesum doesn't get upset by null values. It simply sums everything that's a number and ignores nulls and text values.

[#5]

The RangeSum solution seems to be smarter, I will try something like this : rangesum(StartOfGroup,peek(EventGroup)) ; with StartOfGroup worth 0 or 1.