Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
Hi Dana
if(Peek('state')=state,Peek('group'),RangeSum(1,Peek('group'))) as group;
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
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
Strange, when I tried it first time, all I got was 1 and NULL. Now it works like a charme.
Thank you sooo much