topic Re: Issues with Autonumber() performance in QlikView

Issues with Autonumber() performance

ajipaul03 — Sat, 16 Nov 2024 02:38:26 GMT

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_ID	CUST	PROD	CUST_PROD	REPEAT_BATCH_ID	REPEAT_SEQ
3	C1	XYZ	C1-XYZ	1	1
4	C1	XYZ	C1-XYZ	1	2
6	C1	XYZ	C1-XYZ	2	1
7	C1	XYZ	C1-XYZ	2	2
8	C1	XYZ	C1-XYZ	2	3
9	C1	XYZ	C1-XYZ	2	4
11	C2	ABC	C2-ABC	3	1
12	C2	ABC	C2-ABC	3	2
14	C2	HIJ	C2-HIJ	4	1
15	C2	HIJ	C2-HIJ	4	2
17	C2	XYZ	C2-XYZ	5	1
18	C2	XYZ	C2-XYZ	5	2
19	C2	XYZ	C2-XYZ	5	3

Re: Issues with Autonumber() performance

jonathandienst — Mon, 26 Aug 2019 05:27:49 GMT

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

Re: Issues with Autonumber() performance

ajipaul03 — Mon, 26 Aug 2019 05:41:04 GMT

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

Re: Issues with Autonumber() performance

Vegar — Mon, 26 Aug 2019 06:40:08 GMT

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

‐Vegar

Re: Issues with Autonumber() performance

Vegar — Mon, 26 Aug 2019 06:42:57 GMT

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.

Re: Issues with Autonumber() performance

Arthur_Fong — Mon, 26 Aug 2019 08:02:08 GMT

Hi Aji,

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

Refer qvw attached for the expression used.

Thanks and regards,

Arthur Fong