topic Re: Rowno function in script that resets after a particular count in App Development

Rowno function in script that resets after a particular count

vkish16161 — Thu, 22 Feb 2018 23:26:35 GMT

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

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.

Re: Rowno function in script that resets after a particular count

vkish16161 — Thu, 22 Feb 2018 23:27:34 GMT

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

Re: Rowno function in script that resets after a particular count

Digvijay_Singh — Thu, 22 Feb 2018 23:49:54 GMT

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

Re: Rowno function in script that resets after a particular count

rwunderlich — Fri, 23 Feb 2018 03:15:32 GMT

AutoNumber(RecNo(), Product) as Counter

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Rowno function in script that resets after a particular count

vkish16161 — Fri, 23 Feb 2018 05:41:28 GMT

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

Re: Rowno function in script that resets after a particular count

rwunderlich — Fri, 23 Feb 2018 06:10:45 GMT

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

Re: Rowno function in script that resets after a particular count

vkish16161 — Fri, 23 Feb 2018 15:50:36 GMT

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!

Re: Rowno function in script that resets after a particular count

rwunderlich — Fri, 23 Feb 2018 16:32:22 GMT

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

-Rob

Re: Rowno function in script that resets after a particular count

pablolabbe — Wed, 29 Apr 2020 19:23:25 GMT

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