topic Re: Autonumber Function Issue in App Development

Autonumber Function Issue

erric3210 — Wed, 07 Sep 2022 02:12:26 GMT

Hi Community,

Today I faced something strange issue with Qlik Sense which I'm not actually able to figure out whether it's a issue or I'm making some mistake.

I'm facing issue with Autonumber() function.

Version I'm using: November 2020 Patch 4

Scenario1: When I try to run below script then it executes in 1 minute.

Table1:
LOAD
Id
Id&'_'&Number AS Key
Date(InvoiceDate) AS New_Date,
Quantity,
Amount,
Number
FROM Invoive.qvd(qvd)
Where InvoiceDate >= Date(today()-240);

Scenario2: When I try to run below script then it executes in more than 1 hour.

I just made a small change in script as per the Best practice of Qlik Sense.

Instead of Id&'_'&Number AS key, I changed it to Autonumber(Id&'_'&Number) AS Key

Table2:
LOAD
Id
Autonumber(Id&'_'&Number) AS Key
Date(InvoiceDate) AS New_Date,
Quantity,
Amount,
Number
FROM Invoive.qvd(qvd)
Where InvoiceDate >= Date(today()-240);

Am I making any mistake using Autonumber function.

Please help so that I can fix the issue.

Regards,

Eric

Re: Autonumber Function Issue

MarcoWedel — Tue, 06 Sep 2022 20:14:51 GMT

see Rob's solution in this thread:

https://community.qlik.com/t5/App-Development/AutoNumber-RowNo-MyField-takes-too-long/m-p/1815069#M6...

Re: Autonumber Function Issue

erric3210 — Wed, 07 Sep 2022 02:25:16 GMT

@MarcoWedel Thank You Sir for replying. The link you shared is great but I don't want to use Peek & previous in my code.

Is there a way with Autonumber().

Also, 1 more thing I've used used Autonumber() function in many of my Applications but this is the first time I've faced this issue. Do I need to change the Autonumber Script for all of them.

I tried the solution but it didn't work. Here is my testing:

AAA:
LOAD *, Recno() AS Rec_Id, AutoNumber(Customer_ID&'_'&Shipment_Id) AS Auto_Key INLINE [
Customer_ID, Shipment_Id, Amount
1111, 11-A, 1000
1111, 11-A, 2000
1112, 11-C, 3000
1113, 11-D, 4000
];

BBB:
NOCONCATENATE
LOAD *,
if (Previous(Customer_ID) = Customer_ID and Previous(Shipment_Id) = Shipment_Id, peek('Unique_Value') + 1, 1) as Unique_Value
Resident AAA
Order by Customer_ID, Rec_Id;
Drop Table AAA;

Wrong Result:

Regards,

Eric

Re: Autonumber Function Issue

Lech_Miszkiewicz — Wed, 07 Sep 2022 02:34:48 GMT

Why don't you use Autonumber Command instead of function as shown in @Oleg_Troyansky post https://www.naturalsynergies.com/q-tip-21-autonumber-so-old-so-new-2/

Re: Autonumber Function Issue

erric3210 — Wed, 07 Sep 2022 03:40:29 GMT

Thanks @Lech_Miszkiewicz for sharing the Link.

I find it little tricky. Can you share its example.

What I'm thinking it's better not to use Autonumber() and simply create a composite key using just field names.

It's safe & work 100%

Regards,

eric.

Re: Autonumber Function Issue

Lech_Miszkiewicz — Wed, 07 Sep 2022 04:29:30 GMT

what is the tricky part for you?

you just create normal composite keys as you currently do and at the very end of your script you just put command

Autonumber Key;

and thats it - have you tried that?

Re: Autonumber Function Issue

oskartoivonen — Wed, 07 Sep 2022 08:25:15 GMT

Best practice with autonumber is precisely as @Lech_Miszkiewicz recommends, you use the autonumber command outside the LOAD-statement as the final thing you do before your data model is finished. That way it's also easy to deactivate if you need to access the content of your fields.