Skip to main content

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

No ratings
cancel
Showing results for 
Search instead for 
Did you mean: 
john_wang
Support
Support

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

Last Update:

Feb 21, 2024 3:02:07 AM

Updated By:

john_wang

Created date:

Feb 21, 2024 3:02:07 AM

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)
    john_wang_2-1708501310020.pngjohn_wang_3-1708501389213.png

    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"
    john_wang_4-1708501729373.png
  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.
    john_wang_5-1708501867820.png

Internal Investigation ID(s):

#00145952

Related Content

QnA with Qlik: Qlik Replicate Tips

Environment:

Contributors
Version history
Last update:
‎2024-02-21 03:02 AM
Updated by: