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

[TTH1]

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 

[Arun_Arasu]

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/Custom...

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

Regards,
Arun

[TTH1]

Hi Arun thanks for the response 

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

[Arun_Arasu]

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

[Heinvandenheuvel · Accepted solution]

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