Using LookCapture in CDC

vitalf03 — Tue, 19 Sep 2023 14:27:15 GMT

Is it possible to use Source Lookup in the ''Filter'' tab passing the information in ''Record Selection Condition'' using CDC (Change Data Capture)?

Re: Using LookCapture in CDC

Heinvandenheuvel — Tue, 19 Sep 2023 15:12:51 GMT

yes

Re: Using LookCapture in CDC

vitalf03 — Tue, 19 Sep 2023 16:35:58 GMT

how? you can make a simulation?

i want use it here? you can?

Re: Using LookCapture in CDC

Heinvandenheuvel — Wed, 20 Sep 2023 03:58:49 GMT

Why don't you describe what you are trying to do ?! source_lookup might not be a solution in your case.

Here is a completely contrived - but working - example.

Source table: 'TEST' column ID and some more columns. 7 rows present with ID values :

SQL> select listagg(id,', ') from test; ---> 2000010, 2000011, 2000012, 2000013, 2000030, 2000031, 2000032

Now I create a table to filter on:
SQL> create table my_filter (id number, my_flag varchar(10));
SQL> insert into my_filter values (2000010, 'SKIP');
SQL> insert into my_filter values (2000031, 'SKIP');
SQL> commit;

And in the task, with the help of the Expression Builder I create a Record Selection Condition under Filter:

'SKIP' != coalesce(source_lookup(1000,'ATT_USER','MY_FILTER','MY_FLAG', 'ID=:1',$ID), 'KEEP' )
Note 1) I used coalesce because I only have rows to skip and want to deal with the 'NULL' returned for rows I want to KEEP. Note 2) I don't really need the MY_FLAG column. It's just as example. For my contrived usage I could just return a hardcoded 1 or 0 or the ID itself as for the example only presence or absence count. For you, a special value like a date range, or country code or whatever may be looked up.

After reload the target now has 5 rows with ID values: 2000011, 2000012, 2000013, 2000030, 2000032

Replicate filtered out the 2 rows with id's 2000010 and 2000031 which were present in the lookup table.

hth,

Hein

Re: Using LookCapture in CDC

vitalf03 — Wed, 20 Sep 2023 14:18:15 GMT

worked, thank you!

topic Re: Using LookCapture in CDC in Qlik Replicate

Using LookCapture in CDC

Re: Using LookCapture in CDC

Re: Using LookCapture in CDC

Re: Using LookCapture in CDC

Re: Using LookCapture in CDC