topic Re: non repeating groups with autonumber in QlikView

non repeating groups with autonumber

Thu, 13 Jul 2017 09:44:23 GMT

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

Re: non repeating groups with autonumber

el_aprendiz111 — Thu, 13 Jul 2017 17:18:38 GMT

Hi Dana

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

Re: non repeating groups with autonumber

Thu, 13 Jul 2017 17:59:12 GMT

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

Re: non repeating groups with autonumber

MarcoWedel — Thu, 13 Jul 2017 23:44:02 GMT

Hi,

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

Re: non repeating groups with autonumber

Fri, 14 Jul 2017 05:46:14 GMT

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

Thank you sooo much