Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

non repeating groups with autonumber

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.

rowtsstategroup
131/12/2016 15:00o1
231/12/2016 14:30w2
331/12/2016 14:00w2
431/12/2016 13:30o3
1 Solution

Accepted Solutions
MarcoWedel

Hi,

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

Other solutions might be:

QlikCommunity_Thread_267543_Pic1.JPG

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


or


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

hope this helps

regards

Marco

View solution in original post

4 Replies
el_aprendiz111
Specialist
Specialist

Hi Dana

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


Not applicable
Author

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

Hi,

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

Other solutions might be:

QlikCommunity_Thread_267543_Pic1.JPG

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


or


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

hope this helps

regards

Marco

Not applicable
Author

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

Thank you sooo much