Environment
The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.
Description
Filtering deletes in a replicate task means the task will intercept or ignore delete transactions from the source so as to leave the record intact on the target.
There are 3 ways to filter delete expressions:
1. Simple Soft Delete using Add Column transformation operation_indicator:
| operation_indicator(value_on_delete, value_on_update, value_on_insert) - |
The way to do this is to write an expression that uses the SQLite operation_indicator() function. See the Replicate User Guide Chapter 12 "Customizing Tasks", Section "Using the Expression Builder (for Filters, Transformations, and Global Transformations)", Sub-section "Using Elements in the Expression Builder", Table 12.16 for more information and restrictions on this function.
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 updated the row and marked it as deleted.
The transformation expression 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 "Y".
Note on tasks that use Store Changes Table:
Please check out the article: Qlik_WhitePaper_Deletes_in_C4DW-FINAL_v2.pdf link at the bottom of this article for more information on Soft Delete
Of course if you used Store Changes CDC (with __CT tables or an audit table) instead of (or in addition to) the default Apply Changes CDC then the individual operations would be written to the __CT tables (or audit table, as you've configured Store Changes CDC for the task) on the target endpoint database. But the downside with Store Changes CDC is that you'd need downstream processing to knit the table back on the target.
2. Conditional Soft Delete
How to differentiate between a regular delete operation and a purge operation:
A typical requirement is mark normal source deletes as "D" on the target and mark deletes as "P" on the target that are generated by a "Purge" job on a source table. The following is a code snippet that you would use in the Add Column transformation expression that checks the source user and acts conditionally on that user.
Case $AR_H_USER When 'PurgeUser' then
Operation_indicator("P",NULL,NULL)
Else
Operation_indicator("D",NULL,NULL)
End |
3. Use the Filter tab of the table settings
Under filter conditions, use AR_H_OPERATION then exclude DELETE
Related Content
