Qlik Replicate: Join different tables in source database and filter records in Full Lod and CDC stages

[john_wang]

Sometimes we need to join different tables in the source databases and filter records according to another table records values. In this article we are using Oracle source endpoint, to demonstrate how to build up such a task in Qlik Replicate.

In the below sample task, table testfilter will be replicated from Oracle source database to SQL Server target database. During the replication, the records which value is INACTIVE in table testfiltercondition will be filtered out and are ignored in both Full Load and CDC stages. The two tables are joined by the same primary key column "id".

Detailed information:

1. In Oracle database prepare 2 tables
   
   create table testfilter (id integer not null primary key, name char(20), notes char(200));
   insert into testfilter values (2,'John','ACTIVE will pass the filter');
   insert into testfilter values (3,'Sybase','INACTIVE will be ignored');
   insert into testfilter values (5,'Hana','ACTIVE will pass the filter');
   
   create table testfiltercondition (id integer not null primary key, status char(20));
   insert into testfiltercondition values (2,'ACTIVE');
   insert into testfiltercondition values (3,'INACTIVE');
   insert into testfiltercondition values (5,'ACTIVE');
   
   
   
2. In Qlik Replicate the table testfilter is included in a Full Load and Apply Changes enabled task.
3. In Table Settings --> Transformation, let's add an additional column name "status" which computed expression is:
   source_lookup('NO_CACHING','SCOTT','TESTFILTERCONDITION','STATUS','ID=:1',$ID)
   

   In the above expression, the 2 tables testfilter and testfiltercondition are joined by the PK column "id" in function source_lookup.

4. In Table Settings --> Filter add a filter on the column "status" which value equal to "ACTIVE"
   
5. In target side tables, only rows with "status" equal to "ACTIVE" are replicated. Other rows were filtered out. The filter takes action for both Full Load and CDC stages.
   

Internal Investigation ID(s):

#00145952

Related Content

QnA with Qlik: Qlik Replicate Tips

Environment:

• Qlik Replicate All versions
• Oracle All versions
  Qlik Replicate