Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
ajipaul03
New Contributor II

Issues with Autonumber() performance

Hi Experts,

I am generating a repeat sequence  using Autonumber function.

AutoNumber(RowNo(), CUST_PROD&REPEAT_BATCH_ID) as REPEAT_SEQ

I have around 30 million records and it takes for ever to run( tested with 1 million records and and took 16 hours). Is there any alternate way to generate this sequence?

 

Thanks

Aji.

 

REC_IDCUSTPRODCUST_PRODREPEAT_BATCH_IDREPEAT_SEQ
3C1XYZC1-XYZ11
4C1XYZC1-XYZ12
6C1XYZC1-XYZ21
7C1XYZC1-XYZ22
8C1XYZC1-XYZ23
9C1XYZC1-XYZ24
11C2ABCC2-ABC31
12C2ABCC2-ABC32
14C2HIJC2-HIJ41
15C2HIJC2-HIJ42
17C2XYZC2-XYZ51
18C2XYZC2-XYZ52
19C2XYZC2-XYZ53
Labels (2)
1 Solution

Accepted Solutions
Partner
Partner

Re: Issues with Autonumber() performance

I would recommend you to change from your autonumber approach to an Peek Previous approach. I think you will find it quite faster.

 

‐Vegar

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

5 Replies
MVP
MVP

Re: Issues with Autonumber() performance

Your syntax is incorrect:

AutoNumber(RowNo(), CUST_PROD&REPEAT_BATCH_ID) as REPEAT_SEQ

This numbers the rowno() according to the sequence defined by the concatenated strings. You need this:

AutoNumber(CUST_PROD&REPEAT_BATCH_ID) as REPEAT_SEQ

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
ajipaul03
New Contributor II

Re: Issues with Autonumber() performance

Thanks for the quick reply.

Hmm... shouldn't the AutoNumber(CUST_PROD&REPEAT_BATCH_ID) just replicate the REPEAT_BATCH_ID?
I need to sequence the the each batch staring from 1
Partner
Partner

Re: Issues with Autonumber() performance

I would recommend you to change from your autonumber approach to an Peek Previous approach. I think you will find it quite faster.

 

‐Vegar

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

Partner
Partner

Re: Issues with Autonumber() performance

A second suggestion  is to make your script incremental so you don't need to run 30M rows every time, only the new/changed batches.

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Partner
Partner

Re: Issues with Autonumber() performance

Hi Aji,

Maybe try doing the Repeat_Seq at front end and monitor the performance?

clipboard_image_0.png

Refer qvw attached for the expression used.

Thanks and regards,

Arthur Fong