topic Re: Soft delete a transaction without losing existing column values in Qlik Replicate

Soft delete a transaction without losing existing column values

nigupta — Wed, 27 Sep 2023 02:50:24 GMT

Using operation function, my soft delete flag is working but when delete operation on source becomes update on target it just retains the primary key columns and wipes out all other columns on the target side. How can I retain other columns ?

Example:

Before

EmpId Name Is_Delete

1 John NULL

After

EmpId Name Is_Delete

1 NULL Y [ I lost "Name" value with soft delete flag ]

Re: Soft delete a transaction without losing existing column values

Heinvandenheuvel — Wed, 27 Sep 2023 04:00:19 GMT

That's not supposed to happen in general, but I think there are exceptions, specifically MS SQL using MS-CDC instead of log-reading.

Please indicate the EXACT source configuration used with version and the Replicate version used.

You may want test using "store changes" to see what information is available for Replicate to work with.

Regards, Hein.

Re: Soft delete a transaction without losing existing column values

nigupta — Wed, 27 Sep 2023 12:38:51 GMT

I am using PostgreSql Version 13 as source.

Also, noticed that store change table also did not capture previous value of "Name" .

Re: Soft delete a transaction without losing existing column values

Heinvandenheuvel — Wed, 27 Sep 2023 14:05:16 GMT

The Replicate userguide for Postgres indicates "l Replication of the Before Image is not supported. "

This is confirmed by your Store Changes test. (thanks)

That being the case the current implementation of soft-delete cannot work because the current implementation (in bulk mode) updates all columns. You could try and see if Transactional mode behaves differently. In your tests (single rows, single table) switch logging to TARGET_APPLY VERBOSE to see how Replicate works.

Theoretically it would seem feasible for Replicate to use a dedicated apply method for soft-delete only changing the flag field. That would seems like a valid and viable improvement request. But even if such request is approved it will take a while (months, possibly years) before you'll see it in the field so you may want to look for some alternative or tell the actual customer you cannot offer this feature.

Hein.