topic Re: Global Transformation in replicate in Qlik Replicate

Global Transformation in replicate

suvbin — Fri, 15 Nov 2024 23:50:03 GMT

can we add a new column to every table with load time of the table.

Re: Global Transformation in replicate

john_wang — Mon, 12 Oct 2020 03:01:37 GMT

Now very sure what's your exact requirement. if you simply want to add a new column to every table with latest update time:

1- if the row was never being Insert/Update/Delete but being Full Load only, then the new column value is the 'load time'

2- if the row was ever being I/U/D then the column value is the latest 'update time'

then it's much easier. you can add a global transform with expression:

DATETIME('NOW','localtime')

however if you do not wish to record the latest 'update time' but keep it as 'load time' only then it's some complex.

Regards,

John.

Re: Global Transformation in replicate

suvbin — Mon, 12 Oct 2020 17:53:31 GMT

can we do like this for the load time as shown in below screenshot

Re: Global Transformation in replicate

john_wang — Tue, 13 Oct 2020 03:02:11 GMT

Hi @suvbin ,

Yes. this is what I meant "Global Transformations...".

Regards,

John.

Re: Global Transformation in replicate

suvbin — Tue, 13 Oct 2020 09:14:04 GMT

But you were mentioning, that load time is complex . Can you please explain on that and how to do it

Re: Global Transformation in replicate

john_wang — Fri, 16 Oct 2020 08:30:40 GMT

Hi Suvbin,

sorry for the delay. the simple one is as below.

hope it helps.

Regards,

John.

Re: Global Transformation in replicate

suvbin — Wed, 06 Oct 2021 16:52:18 GMT

how to accomplish the below one...

"however if you do not wish to record the latest 'update time' but keep it as 'load time' only then it's some complex."

Re: Global Transformation in replicate

Nimmi — Mon, 29 Nov 2021 18:59:54 GMT

@john_wang TY John! Simple and elegant Solution!

Question - In the solution, is the date time stored in the field,

a. The time the data comes to Replicate Server

b. The time the data is committed to the target database

The reason I ask is - we are using log stream tasks in our implementation and if there is any downtime in the target database, then there will be a large difference in the timestamps - creation at source and posting into target.

Thanks

Nimmi

Re: Global Transformation in replicate

john_wang — Tue, 30 Nov 2021 07:16:12 GMT

Hi @suvbin ,

Sorry I missed the update.

Regarding "however if you do not wish to record the latest 'update time' but keep it as 'load time' only then it's some complex."

Basically we can use target_lookup() function to get the 'load time' and write it back to the target table whenever an update occurs on the row. This operation may slow down the target apply performance.

Regards,

John.

Re: Global Transformation in replicate

john_wang — Tue, 30 Nov 2021 07:33:38 GMT

Hello @Nimmi ,

The date/time comes from the Replicate Server OS system time.

Please take note that in your scenario:

1. In the "Log Stream Staging task", Global Transformations are not available

2. Based on (1) you can only add the transformation in "Replication task"

3. you are right, if there is any downtime in the target database, then there will be a large time gap

Regards,

John.

Re: Global Transformation in replicate

Nimmi — Tue, 30 Nov 2021 12:49:04 GMT

TY @john_wang !

Question : Is there any way to have Replicate post the time the data is committed to the target database?

Thanks

Nimmi

Re: Global Transformation in replicate

john_wang — Tue, 30 Nov 2021 12:59:23 GMT

Hi @Nimmi ,

If you want to record the exact commit time of the target DB, I think trigger is a better option - no data traffic in the network however please be careful as trigger may slow down the applying performance in target DB .

Hope this helps.

Regards,

John.

Re: Global Transformation in replicate

PICTConversionTeamDevs — Wed, 29 Jun 2022 15:48:05 GMT

We are trying to add two columns with the Global Transformation but it does not add a new column. I enabled the trace and see the below message.
Column 'OCCLCUSR.CCX_AM_BASICRTPOLICYFIELDS.SOURCE_ROW_ACTION_CODE' will not be replicated homogeneously as it was added using a transformation

Support Case

https://community.qlik.com/t5/crmsupport/casepage/issue-id/00043027/issue-guid/5003z00002X2hmpAAB/issue-provider/salesforce

Re: Global Transformation in replicate

Steve_Nguyen — Thu, 30 Jun 2022 16:04:57 GMT

look like you open a support ticket, please work with support team on your case.