topic Provide Complex filter condition in replication task in Qlik Replicate

Provide Complex filter condition in replication task

rakeshraghav29 — Thu, 04 Apr 2024 12:08:51 GMT

Hello,

I am trying to migrate data via full load task from db2 z/os to PostgreSQL and want to know how to provide filter condition like below:

Table to be replicate: employee

Filter condition: select * from employee where deptid in (select deptid from department where region = 'US');

I don't see any option to build a complex filter like above directly in replication task, except I build a view in source with this condition.

Thanks,

Rakesh

Re: Provide Complex filter condition in replication task

john_wang — Thu, 04 Apr 2024 13:15:09 GMT

Hello Rakesh @rakeshraghav29 ,

Welcome to Qlik Community forum and thanks for reaching out here!

Except the option of creation VIEW in DB2z, We might use the "Fullload Passthru Filter" for such complex filter, this is an example how to use it.

Hope this helps.

John.

Re: Provide Complex filter condition in replication task

Heinvandenheuvel — Thu, 04 Apr 2024 16:34:52 GMT

Best option, for full-load only, is really the view as you suggest.

Next best a push-thru filter as per @john_wang

Now if this filtering also needs to be in place for CDC, then there is the 'data enrichment' SOURCE_LOOKUP function which can help you create a filter expression.

This is executed row by row, but with caching option which should alleviate overhead a lot in your case (regions in the US are likely very stable, perhaps occasionally updated overnight.

source_lookup(TTL,SCHM,TBL,EXP,COND,COND_PARAMS)

source_lookup(3600,'your_schema','department','count(1)','deptid = :1 and region = 'US'',$regionid)
TTL would be 3600 (an hour)
Schema
TBL = department
EXP='count(1)'
COND = deptid = :1 and region = 'US'
COND_PARAMS = $regionid

Hein