topic Qlik replicate Issue for Insert and Update scenario in Qlik Replicate

Qlik replicate Issue for Insert and Update scenario

gopikrishnan06 — Wed, 15 Oct 2025 06:11:00 GMT

Hi team,

Adding new column as add column as audit created datetime for insert, Audit updated datetime for the update.

When ever i get the insert command then it need to change the audit created date time. When ever i get the update command it change the audit updated date time.

But here it not working.. When i get insert or update it change the audit created date time alone..

Code:

Audit created date time i used.

Column 'audit_created_datetime', data type DATETIME(6)
        Value: $AR_H_TIMESTAMP

Audit updated Date time i used

Add column
	for %.%
	Column 'audit_last_updated_datetime', data type DATETIME(6)
        Value: datetime('now', 'UTC')

Kindly confirm the best one..

Whenever i need to get insert the current timestamp need to get it..

whenever i need to get update the current timestamp need to get it..

Re: Qlik replicate Issue for Insert and Update scenario

john_wang — Wed, 15 Oct 2025 08:13:35 GMT

Hello @gopikrishnan06 ,

Thanks for the post!

If I understand your question correctly, you want to add two additional columns to the target table:

audit_created_datetime – records the time of a Full Load or INSERT operation (should remain unchanged during UPDATE operations).
audit_last_updated_datetime – records the time of an UPDATE operation (should be NULL or EMPTY during Full Load or INSERT operations).

This can be achieved in Qlik Replicate (for almost databases). However, you’ll need to use the target_lookup function for audit_created_datetime; otherwise, its value will be set to NULL during an UPDATE operation.

Hope this helps.

John.

Re: Qlik replicate Issue for Insert and Update scenario

gopikrishnan06 — Wed, 15 Oct 2025 12:39:55 GMT

Hi John,

Whenever full load or insert the Audit created datetime need to get inserted as UTC..

Whenever Update happened for the old data or new data then Audit Updated datetime need to get updated as UTC... Most of the time (insert ie audit created datetime) as NULL.

To avoid that can you share the suggestions to achieve in qlik replicate

Re: Qlik replicate Issue for Insert and Update scenario

john_wang — Wed, 15 Oct 2025 13:56:44 GMT

Hi @gopikrishnan06 ,

please check article Target Lookup - Insert_Date.

Regards,

John.

Re: Qlik replicate Issue for Insert and Update scenario

gopikrishnan06 — Wed, 15 Oct 2025 14:15:56 GMT

hi John,

Thank you for your comments. Its possible to add in the global transformation. we have more tables.

Re: Qlik replicate Issue for Insert and Update scenario

gopikrishnan06 — Wed, 15 Oct 2025 14:39:03 GMT

John we have more tables in the tasks. Its possible to add the target_lookup in the Global transformation..

Re: Qlik replicate Issue for Insert and Update scenario

Dana_Baldwin — Wed, 15 Oct 2025 15:32:58 GMT

Hi @gopikrishnan06

Target lookup can only be defined at the column level where it will be populated. To make implementing this for multiple columns easier, do one manually then study the format of the exported task file and make further edits to the JSON to add the other lookups - then import the task JSON.

Thanks,

Dana

Re: Qlik replicate Issue for Insert and Update scenario

gopikrishnan06 — Wed, 15 Oct 2025 15:54:12 GMT

Hi Dana, Thank you..

I have 2 columns as common added to all the tables in the Global rules..
If insert appear then it go to add column as Audit_created_datetime.
if Update appear then it go to add column as Audit_updated_datetime.

But here issue whenever i get update it changing the Inserted timestamp.. That alone need to fix it...

if you have any solution just suggest it will helpful

Re: Qlik replicate Issue for Insert and Update scenario

gopikrishnan06 — Thu, 16 Oct 2025 10:35:55 GMT

Hi John,

Is there any other option to do the update Audit Updated_date with out touching the Inserted values Audit created_date..

we have more tables.. Insert working perfect using H_operation.

Here update alone changing the Insert timestamp alone..ie Audit created_date.

If any option to add in global rules. Thank you

Re: Qlik replicate Issue for Insert and Update scenario

john_wang — Thu, 16 Oct 2025 13:35:57 GMT

Hello @gopikrishnan06 ,

As @Dana_Baldwin explained, the lookup function can only be defined at the column level, and it’s used to retain the existing value in a column.

thanks,

John.

Re: Qlik replicate Issue for Insert and Update scenario

gopikrishnan06 — Sun, 19 Oct 2025 03:35:52 GMT

Hi John,

Tired with several ways but nothing work on it for the Update scenario.. Finally moving with the target look up concept.

In a table we have 1 col as PK and few tables have more composite Key..

Need to add for 1PK alone the target look up.

Kindly confirm..

Re: Qlik replicate Issue for Insert and Update scenario

john_wang — Sun, 19 Oct 2025 14:16:03 GMT

Hello @gopikrishnan06 ,

The Lookup() function requires a primary key or unique index; otherwise, it performs a full table scan, which can impact the entire database and significantly degrade lookup performance.

Regards,

John.

Re: Qlik replicate Issue for Insert and Update scenario

gopikrishnan06 — Tue, 21 Oct 2025 10:54:09 GMT

Hi John,

As per Target look up i applied in the column edit but it still not working..

Today i got first update again it make Null the Insert col as Audit created date time..

CASE
WHEN $AR_H_OPERATION = 'INSERT' then
datetime('now')
ELSE
target_lookup('NO_CACHING','qlik_1','JJJJ','audit_created_datetime','aa=:',$aa,'bb=:', $bb)
END

Again the update issues alone facing kindly check and do the needful..

Re: Qlik replicate Issue for Insert and Update scenario

john_wang — Tue, 21 Oct 2025 15:28:18 GMT

Hello @gopikrishnan06 ,

We recommend that you open a support ticket and engage the PS team to help move the project forward smoothly.

Hope this helps.

John.

Re: Qlik replicate Issue for Insert and Update scenario

gopikrishnan06 — Tue, 21 Oct 2025 17:11:16 GMT

Hi John,

what ever the said option have referred it was not working..

So i am checking in the Qlik forums and fix for it.. But here the Target loop up tired with the inserts and Updates too.

This time it changing the inserted ie audit created datetime timestamp to latest. Actual we need only updated datetime for an update.

Re: Qlik replicate Issue for Insert and Update scenario

narendersarva — Tue, 21 Oct 2025 17:16:44 GMT

Hi @gopikrishnan06

As John recommended, please open a Qlik support ticket to look into this further.

Thanks
Naren

Re: Qlik replicate Issue for Insert and Update scenario

gopikrishnan06 — Thu, 23 Oct 2025 05:35:06 GMT

Hi TEam,

Raised Support case how long it will going to test by the support team.. So immediate help so i asked in Forums. But here no scripts are working for the updated columns.

Re: Qlik replicate Issue for Insert and Update scenario

Dana_Baldwin — Thu, 23 Oct 2025 18:11:39 GMT

Hi @gopikrishnan06

I found your support case and updated it from severity 3 to severity 2. I see we requested a diagnostics package on 10/22 that we have not yet received but need before the case can move forward. Please update the case with that.

Thanks,

Dana

Re: Qlik replicate Issue for Insert and Update scenario

gopikrishnan06 — Fri, 24 Oct 2025 02:48:06 GMT

hi Dana,

DPackages already updated.. I am facing in update scenario alone... it got updated as fine.. but issue with it changing the inserted datetime as audit created date

Re: Qlik replicate Issue for Insert and Update scenario

Dana_Baldwin — Fri, 24 Oct 2025 16:16:11 GMT

Thanks @gopikrishnan06 I see you updated the case. It is being reassigned to the next available support engineer. The case is being handled in IST hours. In case you need to escalate please refer to: How to escalate a support case - Qlik Community - 1713475

Thanks,

Dana