Problems with filters to prevent deletion

[JorgeManrique]

Good morning, we have created a filter in our Qlik Replicate for certain tables where the following string has been added:
"$AR_H_OPERATION != 'DELETE'" to prevent deletes from being replicated. After a few days with the filter in place, we’ve seen that these tables are still replicating deletes. Is this correct?

[stijnvaneven]

Hi Jorge,

Your filter syntax looks fin  `$AR_H_OPERATION != 'DELETE'` in the Record Selection Condition is the documented way to suppress deletes on the target, and your screenshot shows it entered correctly.

This could be a UI interpretation issue rather than a filter problem. There's an official Qlik KB article that covers exactly this situation:

https://qlikcrm.my.site.com/QlikHelpCenter/s/article/Qlik-Replicate-Task-is-set-to-Ignore-Delete-Ope...

The short version: in the Change Processing screen, there are two tabs that show different things:

• Recent Activity shows everything that was read from the source transaction log, so deletes will keep showing up here even with the filter in place, because Replicate still reads them, it just doesn't apply them.
• Aggregates shows what was actually applied to the target. With your filter working correctly, you should see zero deletes here.

So if you've been looking at Recent Activity, that explains it. Switch to the Aggregates tab and check there, that's the one that reflects what hit the target.

Hope this helps.

[JorgeManrique]

Hello, the Aggregates tab shows the total data for all the tables that are being replicated; it is not a filter that applies to all tables but only to specific ones. Therefore, in this case, Aggregates continues to show deletes. In the Recent Activity section, if we filter by the tables where we have applied the delete filter, it shows that deletes have been processed.

[stijnvaneven]

You're right that Aggregates shows task-level totals across all tables, so if other tables in the task don't have the delete filter, their deletes will still appear there. Good point.

For Recent Activity though, that's actually exactly the scenario described in the KB article, even when filtered to only the tables that have the delete filter, deletes will still show up there, because Recent Activity shows what was read from the source transaction log. The filter doesn't stop Replicate from reading deletes, it only stops them from being applied to the target. So seeing deletes in Recent Activity for a filtered table is expected behavior, not a sign that the filter isn't working.

Can you do a test to confirm by deleting a known row on the source for one of the filtered tables, wait for the task to process it, then query the target for that primary key. If the row is still there, the filter is working as intended. Or do the test on maybe a separate table?

[JorgeManrique]

I have added up the total number of deletes across all tables and then subtracted from that total the number of deletes for the tables with the filter. As a result, I obtain the number of deletes shown in the Aggregates section, so we can confirm that the filter is working correctly.

[john_wang]

Hello @JorgeManrique 

Would please share what's the source DB type and Replicate version?

thanks,

John.

[JorgeManrique]

Source: DB2 LUW

Qlik Version: November 2023 (2023.11.0.597)