topic How to transformation column auto increment in target data in Qlik Replicate

How to transformation column auto increment in target data

DendyLazuardi — Thu, 23 Jul 2020 04:08:08 GMT

Hello,

I am looking for some help related to transformations. So i want to add more column in target data for field let say "transformid" and in this field i want to use Auto Increment so if there any insert in Source Data, the Target Data will count 1,2,3 etc

Can anyone let me know if this is possible or not? and if possible how do i build expression

Thanks you

Dendy

Re: How to transformation column auto increment in target data

Vegar — Thu, 23 Jul 2020 06:22:58 GMT

If all rows are to be unique then you could use rowno() to get a 1 for the first row, 2 for the second and so on.

LOAD
RowNo() as IncrementID,
FieldA,
FieldB,
Etc
FROM [Source Data];

Edit: I accidentally wrote RecNo() in my orignial script sample, I've changed it to RowNo(). RecNo() will give you the row number of the source table not the target table.

Re: How to transformation column auto increment in target data

john_wang — Thu, 23 Jul 2020 10:20:50 GMT

Hello Dendy,

Are you talking about Qlik Replicate transformation?

It's not difficult in Replicate but I think the best approach is using the database itself auto-increasing functionalities (auto-increasing columns in SQL Server, MySQL, or sequence column in Oracle etc...), the advantage of using DB's functions than using Qlik Replicate transformation are:
1. The task definition is simple and easy maintanence;
2. Gain better performance (you need not do the compute in Replicate and no unnecessary network taffic especailly for huge changes)

I'm not sure if your target DB have such ability. If not we will try to find other options.

thanks,

John WANG.

Re: How to transformation column auto increment in target data

Vegar — Fri, 24 Jul 2020 05:33:16 GMT

@DendyLazuardi

I am sorry. I missed the detail that you where posting in the Qlik Replicate section. My suggestion above might not be a doable solution for you. It could however be useful when working with the QlikView /Qlik Sense script.

Re: How to transformation column auto increment in target data

harsh2 — Fri, 13 Oct 2023 06:51:57 GMT

Hi john,

So can we generate a sequence number within Qlik replicate?

If yes,then can you show us?

Thanks,

Harsh

Re: How to transformation column auto increment in target data

john_wang — Fri, 13 Oct 2023 07:03:11 GMT

Hello @harsh2 ,

Personally, I do not think it's a good approach to generate such a sequence number by Qlik Replicate. It's much better if we can do that in target DB, the advantages are: easier (need not complex expression in Replicate); faster (need not transfer these information via network); stronger (to avoid the duplicate number if any network error or other exceptions).

Regards,

John.

Re: How to transformation column auto increment in target data

AgusBudianto — Mon, 03 Feb 2025 03:03:35 GMT

Hi @john_wang

I am interested in this post, we have tried to add identity column on Azure SQL Server with configuration on column RECORD_ID BIGINT IDENTITY(1,1) NOT NULL, and it works as expected

but we have target table using Azure Databricks Lakehouse (Delta), with configuration column RECORD_ID NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1) the result fails with error message "[DELTA_NOT_NULL_CONSTRAINT_VIOLATED] NOT NULL constraint violation for column: RECORD_ID."

You should know that we have created a ticket to Qlik Support but the issue is still under analysis in the lap

can you help me faster? because this is a production issue

Re: How to transformation column auto increment in target data

john_wang — Tue, 11 Feb 2025 09:22:12 GMT

Hello Anto, @AgusBudianto

Thanks for the update. I'm pasting the update from your another article.

It seems that using the current timestamp is the best approach to identify the latest updated rows. In Databricks, a column defined as GENERATED BY DEFAULT AS IDENTITY cannot be directly updated for existing data. Additionally, it cannot be modified when using the LOAD method to insert records into a table. However, it can be updated through INSERT operations, but Qlik Replicate does not support transactional apply mode for Databricks endpoint.

Regards,

John.

Re: How to transformation column auto increment in target data

AgusBudianto — Wed, 12 Feb 2025 08:15:57 GMT

Thank you @john_wang for convincing us that using current timestamp is the best approach.