topic Re: Table filter expression in Qlik Replicate

Table filter expression

Bob09 — Tue, 08 Feb 2022 21:19:06 GMT

how do you prevent the filter expression from processing a DELETE operation. When my expression processes a delete it causes the table replication to suspend with error message:

Table 'ods_owner'.'ppd_entry_bob' (subtask 0 thread 1) is suspended. Cannot compute expression, not all column values are in the data record.

my expression is: $upsert_user!='qlikreplicate'

where upsert_user is a string field in the ppd_entry_bob table

Re: Table filter expression

lyka — Tue, 08 Feb 2022 21:20:44 GMT

Hello,

Please refer to this article in our community:

https://community.qlik.com/t5/Knowledge/Filter-Deletes-in-Replicate/ta-p/1801945

Thanks

Lyka

Re: Table filter expression

Heinvandenheuvel — Fri, 11 Feb 2022 05:38:45 GMT

I happened to have some background information. I first tested this myself on a msSQL source and it worked fine. This problem is specific to the (unspecified) source database which as it turns out was PostgreSQL.

The Qlik Replicate error message was right on the mark, the column used in the test simply was not in the WAL datastream for a DELETE operation. It was there for INSERTs and UPDATEs.

The workaround/solution is to ALTER TABLE xxx REPLICA IDENTITY FULL (which is relatively high overhead)

Or, add the filter column(s) to the PK Constraint and ALTER TABLE xxx REPLICA IDENTITY USING INDEX pk (ugly)

btw... the filter was a second level filter meant for insert/updates where the first level filter excluded DELETE. One might that the second level is not executed when the first level fails, but that's not the case. The whole filter is first evaluated/parsed (by SQLite).

Hein

Re: Table filter expression

Bob09 — Wed, 11 Mar 2026 19:57:37 GMT

The solution for using a column filter when the table also has DELETES occurring is to change the filter expression to:

COALESCE(LOWER($lastupdtuser), 'N/A') != 'qlikreplicate'