Skip to main content
Announcements
UPGRADE ADVISORY for Qlik Replicate 2024.5: Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
rakeshraghav29
Contributor
Contributor

Provide Complex filter condition in replication task

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

Labels (1)
2 Solutions

Accepted Solutions
john_wang
Support
Support

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.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!

View solution in original post

Heinvandenheuvel
Specialist III
Specialist III

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

View solution in original post

2 Replies
john_wang
Support
Support

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.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
Heinvandenheuvel
Specialist III
Specialist III

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