topic Re: Move Deleted Records into another Table in Qlik Replicate

Move Deleted Records into another Table

RamdasP — Mon, 06 Sep 2021 21:41:33 GMT

Hi,

Using Qlik Attunity Replicate process is it possible to move records that are deleted from the source into a separate table in the target database or set a flag in the target table to indicate that the record was deleted from the source.

Any suggestions will be highly appreciated.

Kind Regards

Ramdas

Re: Move Deleted Records into another Table

john_wang — Mon, 06 Sep 2021 22:31:58 GMT

Hello @RamdasP ,

If you want to keep the 'deleted' rows in the same target table, you may use operation_indicator(value_on_delete, value_on_update, value_on_insert) function.

If you want to put the 'deleted' rows to a separate target table, then you can use multiple tasks. In one task filter out the 'DELETE' operation rows (keep insert and update rows only, but you may have to use UPSERT mode in this task because of PK violation of insert), in another task (store changes only) stores the 'DELETE' operation rows.

Hope this helps,

John.

Re: Move Deleted Records into another Table

RamdasP — Tue, 07 Sep 2021 06:51:46 GMT

Thx, John

Re: Move Deleted Records into another Table

tanhanyan — Fri, 09 May 2025 08:34:00 GMT

Hi @john_wang John,

I have similar task to insert DELETED record from source table to another Archive Table where to keep the history data. Need your help to provide a details step to configure the additional Qlik Replicate Task just to keep deleted record in Archive table.

below step configured but hit error:

1) Table Setting> Filter > Record Selection Condition > $AR_H_OPERATION=='DELETE'

2) Task Setting > Change Processing > Off "Store Changes Setting"

When source deleted record, we hit the error of no record found to perform deletion as the table remain 0 records since all record coming in before are Insert / Update which we do have another task handle the insert and update record to another target table.

Thanks.

Re: Move Deleted Records into another Table

john_wang — Sat, 10 May 2025 08:30:26 GMT

Hello @tanhanyan ,

Thanks for the following up.

When the operation_indicator function is used in a task, it significantly changes the task behavior: records will no longer be deleted from the target endpoint.

For example, if you add a new column, e.g., flag, with a string or integer data type in a table, you can use the following expression:

operation_indicator('Deleted', 'Updated', 'Inserted')

operation_indicator('D','U','I')

The column will then contain values that indicate the type of operation on the source side: DELETE, UPDATE, or INSERT.

If your downstream applications require an integer flag instead, you can use:

operation_indicator('1','2','0')

Let us know if you need any further details or assistance.

Regards,

John.