topic Re: How to create a new column as AUDIT_TYPE in Qlik replicate in Qlik Replicate

How to create a new column as AUDIT_TYPE in Qlik replicate

TTH1 — Thu, 08 Jun 2023 11:15:06 GMT

Hi All,

I need to create a new column as AUDIT_TYPE in that whatever the update data from source should show as UPDATE same for INSERT as well, as below

AUDIT_TYPE

INSERT

UPDATE

INSERT

Re: How to create a new column as AUDIT_TYPE in Qlik replicate

Arun_Arasu — Thu, 08 Jun 2023 11:32:10 GMT

Hello @TTH1 ,

Thanks for reaching out to Qlik community.

If I have understood your question correctly , you are trying to add a column which should track which operation is performed during replication Insert/update/delete , if that is the case , then please refer to the below user guide

https://help.qlik.com/en-US/replicate/May2023/Content/Global_Common/Content/SharedEMReplicate/Customize%20Tasks/tasks_headColumns.htm#anchor-7

You can use the header "AR_H_OPERATION"

Value in Change Processing
Can be one of the following: INSERT, UPDATE, or DELETE.

Value in Full Load
INSERT

You can add a new column thru transform and set the value to reflect from "AR_H_OPERATION"

Please refer below user guide for more information

https://help.qlik.com/en-US/replicate/May2023/Content/Global_Common/Content/SharedEMReplicate/Customize%20Tasks/tasks_useTransTable.htm

Hope the above answers your question

Thanks & Regards

Arun

Re: How to create a new column as AUDIT_TYPE in Qlik replicate

TTH1 — Thu, 08 Jun 2023 12:46:29 GMT

Case

then $AR_H_OPERATION='INSERT' then

VARCHAR(10) 'INSERT'

Will this works how to do it for Update logic

Re: How to create a new column as AUDIT_TYPE in Qlik replicate

Steve_Nguyen — Thu, 08 Jun 2023 13:05:16 GMT

we have a good article forum below :

https://community.qlik.com/t5/Qlik-Replicate/Get-First-Value-on-New-Columns-using-Expression-Builder/td-p/1702715

https://community.qlik.com/t5/Qlik-Replicate/Not-to-perform-cdc-on-a-column/td-p/2063364

Re: How to create a new column as AUDIT_TYPE in Qlik replicate

Heinvandenheuvel — Thu, 08 Jun 2023 13:08:16 GMT

Do I understand it correctly and you desire to use a (global?) table transformation add a column named 'AUDIT_TYPE' and that column should indicate on target how the row data was obtained - through UPDATE or INSERT. Correct?

You'll likely to want to add a USER and DATE-TIME column as well.
You may want to investigate whether the business doesn't really want CHANGE TABLE rows for full audit of the life of a target row, retaining the insert after update(s) and even deleted
You might was to also look at audit tables ( documentation: C.1 Using an Audit table ) but those are probably over the top, very few folks end up using those.
If you also want to audit the DELETE operation then you can use the operation_indicator function which allows you to specify any arbitrary value for insert/update/delete and make a delete become a 'soft' delete = update + flag.

>> Case then $AR_H_OPERATION='INSERT' then VARCHAR(10) 'INSERT'

This allows you to pick any value to represent 'INSERT' but why not just assign $AR_H_OPERATION ? It will do the Insert and Update if that actual wording is as desired which appears to be the case from the original question.

Hein