topic Qlik Replicate Filter Expression in Qlik Replicate

Qlik Replicate Filter Expression

ET_FixEx_RIVA_HSBC1 — Tue, 19 Mar 2024 04:34:57 GMT

Hi everyone,

I wanted to apply the below filter on replicate but dont know whats the correct syntax for that , can someone help me on this

select * from CMS_BDR_AGG_GROUP where keysum not in (select distinct '%X'||agg_id||'X%' as keysum from CMS_REF_AGG where (agg_type=3 and NAME='HBCA') OR (agg_type=2 and NAME like '%HBCA%')) ;

Re: Qlik Replicate Filter Expression

john_wang — Tue, 19 Mar 2024 04:48:12 GMT

Hello @ET_FixEx_RIVA_HSBC1 ,

Thanks for reaching out to Qlik Community!

May we know if this is a Full Load ONLY task, or the filter will be used during CDC stage?

If it's a Full Load ONLY task then the easiest way is create a VIEW in source database (and filter the data by using the VIEW), and replicate the VIEW in Qlik Replicate task. It's much easier in Replicate task design/maintenance and better performance during run time.

Hope this helps.

John.

Re: Qlik Replicate Filter Expression

ET_FixEx_RIVA_HSBC1 — Tue, 19 Mar 2024 05:00:13 GMT

Hi John , Thanks for helping here we need to apply the filters in CDC stage. We are unable to modify the source database. Please advise of a solution.

Re: Qlik Replicate Filter Expression

Heinvandenheuvel — Tue, 19 Mar 2024 13:39:10 GMT

That request appears to be beyond the direct Replicate transformation functions.

Replicate easily can do a convoluted 'CASE - this that or the other thing' but the IN clause suggest infinite/not-predefined cases. Maybe a smart RANGE filter can help if the cases are limited.

I suspect you best, slow, bet is to let the source handle it.

Use a transformation (filter) using source_lookup(TTL,SCHM,TBL,EXP,COND,COND_PARAMS) with something along the lines of

source_lookup(0,<schema>,'CMS_REF_AGG', 'count(1)' ,' (:1=3 and :1='HBCA') OR (:1=2 and :2 like '%HBCA%'))', $agg_type, $NAME)

If the returned count is 0, then it is 'NOT IN'

Hein,