Move Deleted Records into another Table

[RamdasP]

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

[john_wang · Accepted solution]

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.

[RamdasP]

Thx, John

[tanhanyan]

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. 

[john_wang]

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')

or

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.