Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Gysbert_Wassenaar

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

View solution in original post

4 Replies
Gysbert_Wassenaar

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
Author

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.


talk is cheap, supply exceeds demand
Not applicable
Author

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