topic Re: CDCT column transformation in Qlik Replicate

CDCT column transformation

AnujGupta — Wed, 20 Sep 2023 06:32:05 GMT

Hi Team,

I have configured Qlik task source as a orcale and sink as azure databricks. However, I have added one column CDCT values as datetime(). I need to understand if any insert, updates happened I will assume cdct values will change to current datetime so that I can use CDCT column in other transformations.

Is my understanding is correct ?

Thanks

Anuj

Re: CDCT column transformation

john_wang — Wed, 20 Sep 2023 07:11:57 GMT

Hello @AnujGupta ,

You are right. You may add such a column in target side table (which does not exist in source table) to reflect the latest update time of the record.

The expression can be : datetime('now','localtime')

Are you facing any difficulties now?

thanks,

John.

Re: CDCT column transformation

AnujGupta — Wed, 20 Sep 2023 07:19:38 GMT

When I go to Apply changes log it say table are continuously doing Insert/updates but when I am checking my max(cdct) it is same. Not sure if I missed something. Ideally if Monitor table is showing some updates --> cdct should also change continuously but it is not happening properly. Also 11:11AM is last modified on which date it is not menioned in logs shall I assume it should be 20th sep 2023.

Re: CDCT column transformation

john_wang — Wed, 20 Sep 2023 08:18:44 GMT

Hello @AnujGupta ,

Let's use Oracle as source endpoint and SQL Server acts as target endpoint to demonstrate how the CDCT column reflects the latest update time.

1. In table transformation (or global transformation) add an additional column, CDCT

The expression of CDCT is: datetime('now','localtime')

2. Startup the task with Full Load and CDC enabled:

3. After the full load done, we see all rows contains the timestamp, it's the full load time - in accurate, it's the Replicate Server Local time in the first picture.

After an update applied to target, then you can see the CDCT value changed (where id = 2), see the second picture in below:

I hope this helps.

Regards,

John.

Re: CDCT column transformation

AnujGupta — Wed, 27 Sep 2023 05:42:33 GMT

Yes, it is clear for me.

Re: CDCT column transformation

SushilKumar — Wed, 27 Sep 2023 06:56:11 GMT

Hello Team,

If our response has been helpful, please consider clicking "Accept as Solution". This will assist other users in easily finding the answer.

reagrds,
Sushil Kumar