non repeating groups with autonumber

[#1]

Dear Community,

I'm totally stucked here and hope you can help me.

I need to generate non repeating groups shown in column group (table below).

The challenge is: Whenever state (compare table below) changes, the value in group must be increased by +1. If there is no change to the previous row, the group no. must remain the same. Any idea how to archive this?

I tried to archive this changes using functions like previous(), peek() and autonumber(), but unfortunately I don't get the desired result.

Loops did return the expected result, but I get unacceptable Performance issues (only 10 rows per second).

Many thanks in advance for every hint.

row	ts	state	group
1	31/12/2016 15:00	o	1
2	31/12/2016 14:30	w	2
3	31/12/2016 14:00	w	2
4	31/12/2016 13:30	o	3

[el_aprendiz111]

Hi Dana

if(Peek('state')=state,Peek('group'),RangeSum(1,Peek('group'))) as group;
  

[#3]

Hi Fer Fer,

almost there :-).

The column group is empty on load and Needs to be generated. But, with a small modification of  your suggestion, it is almost perfect ...

Since I don't care, if the numbering is in any logical order, I came up with adding RecNo() to get a unique number.

f(Peek('state')=state,'',RangeSum(1,Peek('group'))+RecNo()) as group2,

But that leaves me with gaps on the repetition (example row 3). Any chance to get same number for every following previous(state)=state in one step?

many thanks

[MarcoWedel · Accepted solution]

Hi,

actually it is Fer's solution that delivers your specified result.

Other solutions might be:

RangeSum(Peek(group),-(state<>Previous(state))) as group

or

Alt(Peek(group),0)-(state<>Previous(state)) as group

hope this helps

regards

Marco

[#5]

Strange, when I tried it first time, all I got was 1 and NULL. Now it works like a charme.

Thank you sooo much