topic Re: increment field on dimension changes in QlikView

increment field on dimension changes

Mon, 18 May 2015 13:47:35 GMT

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.

Re: increment field on dimension changes

Gysbert_Wassenaar — Mon, 18 May 2015 13:56:02 GMT

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

Re: increment field on dimension changes

Mon, 18 May 2015 14:30:10 GMT

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;

Re: increment field on dimension changes

Gysbert_Wassenaar — Mon, 18 May 2015 14:36:25 GMT

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.

Re: increment field on dimension changes

Mon, 18 May 2015 18:07:26 GMT

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