topic Re: operation_indicator('DELETE', 'UPDATE', 'INSERT') vs header_operation in change table in Qlik Replicate

operation_indicator('DELETE', 'UPDATE', 'INSERT') vs header_operation in change table

pkagel — Wed, 17 Aug 2022 16:29:44 GMT

So I am seeing some weird data . I have some records being brought over That show the Operation_indicator as Delete but in the Change Tracking table that stores the changes the Header_Operation shows it as an update. Attached file to show those results

Re: operation_indicator('DELETE', 'UPDATE', 'INSERT') vs header_operation in change table

shashi_holla — Wed, 17 Aug 2022 16:43:45 GMT

@pkagel That behavior is normal. Please read the excerpt from the user guide:

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

Operation

The following table describes the Operation functions used by the Expression Builder in Qlik Replicate .

Operation functions
Function	Description
operation_indicator(value_on_delete, value_on_update, value_on_insert)	When the operation_indicator function is invoked on its own or as part of an expression, records deleted from the source endpoint will not be deleted from the target endpoint. Instead, the corresponding target record will be flagged (with a user-provided value) to indicate that it was deleted from the source. The operation_indicator function also requires you to provide values to indicate records that were inserted or updated in the source endpoint. Note: The operation_indicator function is not supported on tables that do not have a Primary Key. Note: It is recommended to add a dedicated column for the flag values, for example, OPERATION. For an explanation of how to add a column, see Using the Transform tab. To specify the function values: Replace value_on_delete, value_on_insert and value_on_update with the values that you want to appear in the target endpoint. Values should be formatted according to the corresponding column type. Example when the column type is INT4: operation_indicator(’1’, ’0’, ’0’) Example when the column type is STRING: operation_indicator(’Deleted’, ’Updated’, ’Inserted’)

Re: operation_indicator('DELETE', 'UPDATE', 'INSERT') vs header_operation in change table

pkagel — Wed, 17 Aug 2022 17:39:25 GMT

Why would this be normal? If I specify the value on Delete to be Delete then why would it be different then the other operation? I feel like I am missing something here.

Re: operation_indicator('DELETE', 'UPDATE', 'INSERT') vs header_operation in change table

Michael_Litz — Wed, 17 Aug 2022 17:47:41 GMT

Hi Pkagel,

Yes, we can do what we call a soft delete where the task intercepts the delete operation. We typically accomplish this through a global transformation , or a table based transformation where we add a column to the target called Deleted_Flag - char(1) and then in the transformation we intercept the delete operation and set the added column to a 'D'. In this way you have not physically deleted the target row, but have marked it as deleted.

The transformation is Operation_indicator("D",NULL,NULL)

The expression for this column transformation is a task function that will basically intercept the delete from happening on the target and turn it into an update instead. As part of this update the value in the "Delete_Flag" column is changed to "D".

Re: operation_indicator('DELETE', 'UPDATE', 'INSERT') vs header_operation in change table

pkagel — Wed, 17 Aug 2022 17:55:10 GMT

But why would this be different than what the header operation is. Why would a delete be marked as an update in the header_operation when it should be a hard delete? Which is why the interception is changing it to a soft delete.

Re: operation_indicator('DELETE', 'UPDATE', 'INSERT') vs header_operation in change table

Michael_Litz — Wed, 17 Aug 2022 18:00:16 GMT

Hi Pkagel,

The header operation (is indicating a delete happened on thye source. When you have coded a operation indicator transformation on a table then there will be no more deletes issued to the target side.

Effectively the source side delete is turned into a target side update - there by keeping the record in the target

We are not changing what happens on the source (or what is captured) in the transaction log - we are changing the transaction to update on the target side only.

Thanks,
Michael

Re: operation_indicator('DELETE', 'UPDATE', 'INSERT') vs header_operation in change table

Michael_Litz — Wed, 17 Aug 2022 18:31:40 GMT

Hi Pkagel,

It occurred to me that maybe the task is set to what we call upsert/merge which can change either an insert or update into a delete / insert pair on the target.

Please check out this link for some additional information on upsert/merge:
https://community.qlik.com/t5/Knowledge/Qlik-Replicate-Implementing-UPSERT-and-MERGE-modes-by-applying-a/ta-p/1780009

Thanks,
Michael

Re: operation_indicator('DELETE', 'UPDATE', 'INSERT') vs header_operation in change table

serge-bouschet — Wed, 03 May 2023 12:38:37 GMT

Hi Shashi_holla

Are tables expected to be fully reloaded after such operation is added as a transformation to a table?

If yes, is the reload triggered manually or happening when resuming the task?

Ideally there's a workaround to full reload because I am trying to set this up on large tables that are already loaded.

Thanks!

Re: operation_indicator('DELETE', 'UPDATE', 'INSERT') vs header_operation in change table

shashi_holla — Wed, 03 May 2023 14:26:43 GMT

No, there is no necessity to do a Full Load. The operation indicator is on delete operations which is during CDC and any new deletes on the source table will get transformed as an update on the target.

Thank you,