Rowno function in script that resets after a particular count

[vkish16161]

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.

[vkish16161]

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

[Digvijay_Singh]

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

[rwunderlich · Accepted solution]

AutoNumber(RecNo(), Product) as Counter

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[vkish16161]

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

[rwunderlich]

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

[vkish16161]

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!

[rwunderlich]

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

-Rob

[pablolabbe]

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