topic Re: Question on Update Matching Logic with Transformed Values in Qlik Replicate

Question on Update Matching Logic with Transformed Values

EE_ — Thu, 29 May 2025 09:45:48 GMT

Dear Qlik Support Team,

We would like to clarify a question regarding update behavior when transformations (such as TRIM) are applied in Qlik Replicate.

Scenario:
If a value in column A1 of table A in the source journal is updated (e.g., from "A " to "B"), and Qlik Replicate applies a transformation (e.g., TRIM) before writing to the target Oracle database, what value does Qlik use in its update condition — the original source value "A " or the transformed value "A"?

Currently, we have confirmed that the target Oracle table may have been manually updated. We also noticed the following message:

"Source changes that would have no impact were not applied to the target database. Refer to the 'attrep_apply_exceptions' table for details."

When reviewing the attrep_apply_exceptions table, we see that the UPDATE statements include WHERE clauses based on the original source data (from AS400).

Given this, we would like to confirm:

If the target Oracle table already contains a trimmed version of the data (e.g., "A"), and the source update is based on "A " (with a trailing space), will Qlik still be able to match the record and apply the update?
Or would this cause a mismatch due to the transformation, thus preventing the WHERE condition from finding the correct target row?

We appreciate your help in clarifying this behavior so we can better understand how Qlik handles updates when transformations are involved.

Looking forward to your guidance.

Re: Question on Update Matching Logic with Transformed Values

john_wang — Thu, 29 May 2025 10:03:02 GMT

Hello @EE_ ,

You opened a similar article 3 weeks ago: Spaces from Source DB2 iSeries Not Transferred

Please check and feel free to let me know if you need any additional assistance.

Regards,

John.

Re: Question on Update Matching Logic with Transformed Values

EE_ — Tue, 03 Jun 2025 05:51:06 GMT

Hello @john_wang ,

Thank you for your response. However, we would like to clarify our original question, as it may have been misunderstood.

Our intention is not to ask how to retain trailing spaces in the target.

Instead, we would like to confirm what values Qlik Replicate uses in the WHERE clause when performing an UPDATE operation on the source, in scenarios where a transformation (such as TRIM) is applied on Qlik.

To reiterate the scenario:

The source (AS400) issues an UPDATE on column A1 from "A " to "B".
A transformation (e.g., TRIM) is applied to column A1 in Qlik Replicate.
The target (Oracle) may already contain "A" due to previous transformation logic.

Our question is:

When applying the update on the source, does Qlik use the original source value ("A ") or the transformed value ("A") in the WHERE clause to identify the target row?

This detail is critical for us to understand whether updates might fail to match the correct target row due to transformation logic.

We appreciate your support in clarifying this specific behavior.

Re: Question on Update Matching Logic with Transformed Values

john_wang — Tue, 03 Jun 2025 14:29:39 GMT

Hello @EE_ ,

Thank you for the clarification.

When applying the update on the source, does Qlik use the original source value ("A ") or the transformed value ("A") in the WHERE clause to identify the target row?

Whether the source column is included in the WHERE clause depends on the presence of a Primary Key, Unique Index, or RRN (Relative Record Number) on the target side.

If you can provide more details about your scenario, I’ll be happy to confirm the expected behavior for you.

Regards,

John.

Re: Question on Update Matching Logic with Transformed Values

EE_ — Wed, 11 Jun 2025 07:30:10 GMT

Hello @john_wang ,

In my scenario, the target does not have a Primary Key, Unique Index, or Relative Record Number (RRN). In this case, I would like to confirm whether Qlik compares all column values to identify the row that needs to be updated.

Additionally, if a transform is applied to column A (e.g., using trim to remove spaces), does Qlik use the original source value (before the transform) or the transformed value in the WHERE clause during the update operation?

We appreciate your support in clarifying this specific behavior.

Re: Question on Update Matching Logic with Transformed Values

john_wang — Sat, 14 Jun 2025 14:45:43 GMT

Hello @EE_ ,

We are referring to the following scenario:

1. AS400 source data type: VARCHAR
2. Oracle target data type: CHAR
3. The results are the same regardless of the internal parameter keepCharTrailingSpaces
4. Trim() transformation is applied to the column
5. During the CDC phase, the column values used in the WHERE clause do not include trailing spaces

If you need to test other scenarios, you can enable the TARGET_APPLY parameter to Verbose and check the task log to review the generated SQL statements.

Hope this helps.

John.