Qlik Replicate Filter Expression

[ET_FixEx_RIVA_HSBC1]

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%')) ;

[john_wang]

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.

[ET_FixEx_RIVA_HSBC1]

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.

[Heinvandenheuvel]

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,