Unable to differentiate between before image and after image in case of upgrade using $AR_H_OPERATION in global transformation in version 2022.5.0.3

[chandankumar]

Previous version : 7.0.0.652
Adding a new column as below using global transformation created in this version:

Add column
for %.%
Column 'AUD_TYPE', data type STRING(50) and sub type Regular
Value: coalesce($AR_H_OPERATION,'BEFOREIMAGE')

Used to load NULL for BEFORE IMAGE in the 'AUD_TYPE' in case of an update and 'UPDATE' to indicate after image.

recently upgraded to below version.

Current version: 2022.5.0.395

Still same transaformation:
Add column
for %.%
Column 'AUD_TYPE', data type STRING(50) and sub type Regular
Value: coalesce($AR_H_OPERATION,'BEFOREIMAGE')

Looks like for the before and after update image the AUD_TYPE is coming same as 'UPDATE' and team is not able to identify which is before and which one is after image.

Is this a known issue , how to differentiate between the BEFORE and AFTER IMAGE for an UPDATE

##Qlik replicate , ##version 2022.5.0.395 , ##AUD_TYPE , ##BEFOREIMAGE ,#$AR_H_OPERATION,

[chandankumar]

Due to the current application design we cannot use the HEADER columns and need to use $AR_H_OPERATION for getting the operation in  AUD_TYPE column

[chandankumar]

I am unable to understand the logic for below table for transactiosn where AUD_TYPE:STRING(50) =  $AR_H_OPERATION .

For both before and after image the $AR_H_OPERATION  is Update.

header__change_seq:STRING(35)	header__change_oper:STRING(1)	header__operation:STRING(12)	AUD_TYPE:STRING(50)
2.02E+34	I	INSERT	INSERT
2.02E+34	I	INSERT	INSERT
2.02E+34	I	INSERT	INSERT
2.02E+34	B	BEFOREIMAGE	UPDATE
2.02E+34	U	UPDATE	UPDATE
2.02E+34	B	BEFOREIMAGE	UPDATE
2.02E+34	U	UPDATE	UPDATE
2.02E+34	B	BEFOREIMAGE	UPDATE
2.02E+34	U	UPDATE	UPDATE
2.02E+34	B	BEFOREIMAGE	UPDATE
2.02E+34	U	UPDATE	UPDATE

[Heinvandenheuvel]

I don't understand the problem.

AR_H_OPERATION is documented to be "Can be one of the following: INSERT, UPDATE, or DELETE"

If it ever was NULL, then there was a bug. This variable and its values reflects what the application requested. 'Beforeimage' is not what the application requested but a Replicate artifact. 

>> Due to the current application design we cannot use the HEADER columns and need to use $AR_H_OPERATION for getting the operation in  AUD_TYPE column

Your design appears to be broken and needs to be fixed. You'll will need to use the header__operation field which is not available for transformations. You _could_ potentially used a target sider computed-by AUD_TYPE column, or defined that as synonym for the header__operation column

Just one opinion. 

Good luck,

Hein

[lyka · Accepted solution]

Good Day!

The problem has been identified as originated from reliance on a product issue where $AR_H_OPERATION was incorrectly informed for the "Before Image" records in V7 of Qlik Replicate and this coincided with the configuration where all of the header columns have been disabled. Once Replicate was upgraded to the latest version, the logic got broken as the software issues was fixed.

To fix the issue the following approach has been suggested:
- Stop task
- change Glob. Transf. (GT) table rename
- remove a GT that adds a AUD_TYPE column
- enable an "operation" column in "Store Changes Settings"-->"Select Change Table Header Columns"
- ensure that the "Store Changes Settings" CDC is set up to "Keep old changes and store new changes in existing change table"
- Rename the target CT table
- Rename target table AUD_TYPE column to "operation__header"
- create a view with a name matching the old CT table name and mapping "operation__header" column to both "OPERATION_HEADER" and "AUD_TYPE"
- Provide permissions to the users to select from the view
- start the task

Hope this helps!

Thanks

Lyka