Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

increment field on dimension changes

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 EventEventGroup
01/01/2015 Pierre A1
02/01/2015 Pierre A1
03/01/2015 Pierre B2
04/01/2015 Pierre C3
05/01/2015 Pierre C3
06/01/2015 Pierre A4
01/01/2015 Rachid A5
02/01/2015 Rachid D6
03/01/2015 Rachid D6
04/01/2015 Rachid D6
05/01/2015 Rachid E7
06/01/2015 Rachid E7

Thanks,

Xavier.

1 Solution

Accepted Solutions

Re: increment field on dimension changes

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


talk is cheap, supply exceeds demand
4 Replies

Re: increment field on dimension changes

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


talk is cheap, supply exceeds demand
Not applicable

Re: increment field on dimension changes

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

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.


talk is cheap, supply exceeds demand
Not applicable

Re: increment field on dimension changes

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

Community Browser