topic Re: Not to perform cdc on a column in Qlik Replicate

Not to perform cdc on a column

suvbin — Sun, 23 Apr 2023 08:10:21 GMT

Hi Team,

If i want to avoid performing cdc on a particular column or table, then how can i do it in Qlik replicate.

I mean to say , after full load, cdc will start right ? so while cdc is in process, the data in few tables and columns should not change.

Could you please let me know if its possible or not ? If so how?

Thanks.

Re: Not to perform cdc on a column

Shai_E — Sun, 23 Apr 2023 14:25:48 GMT

Hello,

I managed to filter CDC for a table using the Filter functionality, under:

Double click on a table in task table selection ->Table Settings -> Filter -> click on Expression Builder

So, for example i used the headers section and chose the header value: 'AR_H_OPERATION'

Which is the CDC operation value.

$AR_H_OPERATION == 'INSERT' OR $AR_H_OPERATION == 'UPDATE' OR $AR_H_OPERATION == 'DELETE'

Shorter version: $AR_H_OPERATION IN ('INSERT', 'UPDATE', 'DELETE')

(Reads as if i did a change on the source, filter it out, could be any of the three options)

Another example:

length($AR_H_USER) == 0

(Only if there is no user replicate the records, could happen only in full load mode, for cdc this column always has a value)

Another option outside of filtering is:

If the tables don't get changed on the source database, you could always designate those tables into a separate full load only task mode.

I would like to point out two things:

1. It doesn't really stop the cdc process only filters the records based on the expression so that you won't get changes.

2. The filter functionality also dictates the full load, so when designing expressions, the filter functionality can dictate record replication for both full load and cdc, if you need a filter for full load only you will need to use the passthrough filter.

These filter expressions are for the whole table, most likely there is also a way to include a specific column in these expressions.

I would advise testing the above expressions in a test enviroment.

Re: Not to perform cdc on a column

Jon_Donker — Mon, 24 Apr 2023 00:21:06 GMT

Our business case is to only select particular changes in a table; instead of a whole table so we use a filter like this:

($AR_H_OPERATION != 'UPDATE') OR ( ($AR_H_OPERATION == 'UPDATE') AND ( ( $BI__FIELD_1 != $FIELD_1 ) OR ( $BI__FIELD_1 IS NULL AND $FIELD_1 IS NOT NULL ) OR ( $BI__FIELD_1 IS NOT NULL AND $FIELD_1 IS NULL ) OR ( $BI__FIELD_2 != $FIELD_2 ) OR ( $BI__FIELD_2 IS NULL AND $FIELD_2 IS NOT NULL ) OR ( $BI__FIELD_2 IS NOT NULL AND $FIELD_2 IS NULL ) OR /*( $BI__DONT_CAPTURE_THIS_FIELD != $DONT_CAPTURE_THIS_FIELD ) OR ( $BI__DONT_CAPTURE_THIS_FIELD IS NULL AND $DONT_CAPTURE_THIS_FIELD IS NOT NULL ) OR ( $BI__DONT_CAPTURE_THIS_FIELD IS NOT NULL AND $DONT_CAPTURE_THIS_FIELD IS NULL ) OR*/ ( $BI__FIELD_4 != $FIELD_4 ) OR ( $BI__FIELD_4 IS NULL AND $FIELD_4 IS NOT NULL ) OR ( $BI__FIELD_4 IS NOT NULL AND $FIELD_4 IS NULL ) ) )

It can get a bit clunky with wide tables. I developed a script to take a list of fields that we needed and create the filter to reduce human error creating the filter manually.

Re: Not to perform cdc on a column

Bill_Steinagle — Mon, 01 May 2023 18:00:05 GMT

@suvbin

Hello thank you for the post to the QDI Forums. As noted by Shai were you able to test out the post to this question and share the results? The option for this process or your requirement as Shai posted would be a Full Load only Task and a CDC only Task per the process. If this needs more in depth research I would request you open a Support ticket with the QDI Team and work with one of our TSE's on the case.

Note: As per your Expression if the management of the Columns is being done you could add the Operations Insert/Update/Delete in the Expression via Case statements.

Thanks,

Bill