Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

Rowno function in script that resets after a particular count

All,


Here's my requirement


Should be done in script.

From:

Product, Value, RowNo

A,10,1

A,,20,2

B,30,3

B,40,4

to

Product, Value, RowNo

A,10,1

A,,20,2

B,30,1

B,40,2

Basically for each value of Product, the rowno() should reset and start from 1.

1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

8 Replies
Highlighted
Creator III
Creator III

Currently trying with Peek. Let me know if you guys can solve it more quickly.

Highlighted
Master III
Master III

I think Peek is needed in script -

Source:

Load * inline [

Product, Value


A,10


A,20


B,30


B,40];


NoConcatenate


Final:

Load *,

if(Product=Previous(Product),Rangesum(Peek(Row_No),1),1) as Row_No

Resident Source;


Drop table Source

Highlighted
MVP & Luminary
MVP & Luminary

Highlighted
Creator III
Creator III

Excellent solution. I did this using 2 for next loops. Your solution is the best one liner ever rwunderlich

Highlighted
MVP & Luminary
MVP & Luminary

It's a good technique using AutoNumber.  Note that it can be slow if there are many thousands of different Product values, in which case you revert to the previous/peek method.

-Rob

Highlighted
Creator III
Creator III

Just a quick clarification,

Here the product field is acting like the Auto ID right?

I would be happy if you could also explain what Auto ID does?

Thanks Rob!

Highlighted
MVP & Luminary
MVP & Luminary

Yes, Product is acting as the AutoId.  AutoId establishes a separate counting sequence for each value of the AutoId.

-Rob

Highlighted
Luminary
Luminary

Thanks Rob 😁

Reviewing the documentation updated so far, there is no clear instruction about Autoid parameter can be a table field. To me I supposed it can only be string parameter inside single quotes.

As you commented, autonumber could be very slow compared to using previous and peek. 

A colleague made a test on a 7 million rows dataset with thousands of distinct values on the key field.

Using autonumber, it took 50 minutes to process

Using sort/peek/previous it took only 4 minutes