topic Re: operation_indicator('D', 'U', 'I') is producing null values in target in Qlik Replicate

operation_indicator('D', 'U', 'I') is producing null values in target

TTH1 — Tue, 18 Jul 2023 10:47:46 GMT

Hi All,

While loading full load in to target I have added operation_indicator('D', 'U', 'I') as audit column, it produced only null it suppose to be " I "if it is full load.

Please assist

Re: operation_indicator('D', 'U', 'I') is producing null values in target

Arun_Arasu — Tue, 18 Jul 2023 11:04:30 GMT

Hi @TTH1 ,

Thank you for reaching out to the Qlik community!

Could you please provide assistance regarding the target endpoint? I attempted to replicate to a PostgreSQL endpoint by adding a column called "operation" and assigning the value from "AR_H_OPERATION".

Once the full load was completed, I observed that the column "operation" contained the value "INSERT".

This transformation is also mentioned in the user guide provided below:

https://help.qlik.com/en-US/replicate/May2023/Content/Global_Common/Content/SharedEMReplicate/Customize%20Tasks/tasks_headColumns.htm#anchor-7

For better assistance, please provide details about the transformations you performed on your task.

Regards,
Arun

Re: operation_indicator('D', 'U', 'I') is producing null values in target

TTH1 — Tue, 18 Jul 2023 12:42:00 GMT

Hi Arun thanks for the response

AR_H_OPERATION will produce ('INSERT','UPDATE' AND 'DELETE') but i need output as ('I','U' and 'D')

Re: operation_indicator('D', 'U', 'I') is producing null values in target

Arun_Arasu — Tue, 18 Jul 2023 13:02:41 GMT

Hi @TTH1 ,

In that case , I would recommend you to consider the below transformation.

CASE $AR_H_OPERATION
WHEN 'INSERT' THEN 'I'
WHEN 'DELETE' THEN 'D'
WHEN 'UPDATE' THEN 'U'
ELSE NULL
END

Please test it and let us know if that helps with your requirement

Regards

Arun

Re: operation_indicator('D', 'U', 'I') is producing null values in target

Heinvandenheuvel — Tue, 18 Jul 2023 17:01:44 GMT

Why not simple use the simple transformation expression: substr($AR_H_OPERATION,1,1)

That will produce I, D, U for INSERT, DELETE, UPDATE

You do NOT want to use 'operation_indicator' as this is strictly created for CDC processing and WILL change the CDC semantics changing delete to update. It does NOT have a document result for full-load but i suppose you could transform the null you observed to something else.

Note, many other customer like to show data provided by full-load as distinct from subsequent Inserts. Older topics in this forum show how a conditional expression using $AR_H_TRANSACTION_ID can be used to distinguish the two INSERT values.

Hein