Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
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