Transformation - Operation Indicator - Archive User - Soft Delete

Michael_Litz — Fri, 19 Aug 2022 17:24:28 GMT

Environment

Qlik Replicate
Task/Table transformation
Operation_Indicator

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.

Why you may want to implement conditional soft delete on a table:

I have seen several implementation of the Operation_Indicator function to enable "Soft Delete" on target side tables. In this example I will wrap the Operation_Indicator function with a case statement that checks the specific user that has performed the delete on the source side.

The reason for this is to allow a regular delete of a record, made by any source side user, to actually delete the record in the target and for a specific user "ArchiveUser" to turn the delete into a soft delete and just mark the record as deleted.

With this transformation in place when an archive job is run on the source the target table will keep the records while they get deleted on the source. The scenario that this is used in is when the source side table has many months/years of old data that is going to be purged or archived. It will be deleted from the source by a monthly job run by a specific user "ArchiveUser". For business needs they do not want these archived records to be deleted from the target.

How the operation indicator works to achieve the soft delete:

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 'Y'. In this way you have not physically deleted the target row, you have intercepted the delete from the source and changed it to an update on the target, and have marked it as deleted.

The general form of the function:

operation_indicator(value_on_delete, value_on_update, value_on_insert)

The transformation function is Operation_indicator("Y",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 "Deleted_Flag" column is changed to "Y".

Where to define a table level soft delete:

Define the table level soft delete in Table Settings transform screen using the expression builder. Note the two extra columns added to the table. LoggedInUser and Deleted_Flag. First we will look at the expression for the newly added field "Delete_Flag" and then we will look at the "LoggedInUser" field and its expression.

Screen Shot of the Table Settings - Showing added field "Deleted_Flag":

Screen Shot of the Expression Builder - Showing the expression for the soft delete:

Screen Shot of the Table Settings - Showing added field "LoggedInUser":

Screen Shot of the Expression Builder - Showing the expression for the LoggedInUser" field:

Orders Table before delete operation is done on the source by the "ArchiveUser":

Results after a delete operation is done on the source by the "ArchiveUser":

You will see that the "Deleted_Flag" is set to "Y" and the LoggedInUser" field is set the "ArchiveUser"

Summary:

The Soft Delete can be accomplished using the operation indicator function on the transformation screen.
In this example we only converted the deletes for a specific user "ArchiveUser"
In this way you have not physically deleted the target row, you have intercepted the delete from the source and changed it to an update on the target, and have marked it as deleted.

article Transformation - Operation Indicator - Archive User - Soft Delete in Official Support Articles