Qlik replicate Issue for Insert and Update scenario

[gopikrishnan06]

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..

[john_wang]

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.

[gopikrishnan06]

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

[john_wang]

Hi @gopikrishnan06 ,

please check article Target Lookup - Insert_Date. 

Regards,

John.

[gopikrishnan06]

hi John,

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

[gopikrishnan06]

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

[Dana_Baldwin]

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

[gopikrishnan06]

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

[gopikrishnan06]

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

[john_wang]

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.