Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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 ];
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 ];
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;
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.
The RangeSum solution seems to be smarter, I will try something like this : rangesum(StartOfGroup,peek(EventGroup)) ; with StartOfGroup worth 0 or 1.