Unexpected MERGE behavior: UPDATE becomes INSERT after CONCATENATE

[Katlitipkins]

Hi everyone,

I am trying to understand an unexpected behavior of MERGE during a normal reload. In the example below, the first MERGE works as expected and updates an existing row. Then I add another row to the same target table using a regular CONCATENATE. After that, I run another MERGE with operation U for the newly added row.

Although the row already exists in the target table, Qlik reports the operation as an insert rather than an update.

Here is a minimal example:

Target:
LOAD *
INLINE [
    Key1, Key2, Attr
    1, p1, a
];


// Key 1 / p1 already exists.
// This is processed as an update, as expected.
MERGE ON Key1, Key2 CONCATENATE (Target)
LOAD
    'U'  AS Operation,
    1    AS Key1,
    'p1' AS Key2,
    'x1' AS Attr
AUTOGENERATE 1;


// Add a new row using a regular CONCATENATE.
CONCATENATE (Target)
LOAD
    2    AS Key1,
    'p1' AS Key2,
    'b'  AS Attr
AUTOGENERATE 1;


// Key 2 / p1 now exists in Target.
// I expected this to be processed as an update.
MERGE ON Key1, Key2 CONCATENATE (Target)
LOAD
    'U'  AS Operation,
    2    AS Key1,
    'p1' AS Key2,
    'x2' AS Attr
AUTOGENERATE 1;

The reload log shows:

First MERGE:
Merged updates: 1
Merged inserts: 0
Merged deletes: 0

Second MERGE:
Merged updates: 0
Merged inserts: 1
Merged deletes: 0

I expected the second result to be:

Merged updates: 1
Merged inserts: 0

The row with Key1 = 2 and Key2 = 'p1' is already present in Target before the second MERGE, but the U operation is still counted as an insert.

Is this expected behavior during a normal reload?

Does MERGE only recognize records that existed before the first MERGE statement? Or is mixing regular CONCATENATE statements with subsequent MERGE statements against the same table unsupported?

I could not find this specific behavior in the documentation, so any explanation would be appreciated.

Qlik Sense version: November 2025

Thanks!

[TheLazyDeveloper · Accepted solution]

Hey boss, 

It's because MERGE isn't searching the live Target table. 

MERGE builds an index of the target keys before it begins applying changes. That index isn't refreshed simply because another CONCATENATE occurred later in the script.

So when the second MERGE executes, its lookup index still effectively contains only '(1,p1)' it doesnt know about '(2,p1)'.  Therefore UPDATE (2,p1) becomes INSERT (2,p1). 

[TheLazyDeveloper]

Here is the same script but instead of the concatenate we just add the values in the initial target table. Notice how the two MERGE now updates both rows instead of inserting a new row. 

Target:
LOAD *
INLINE [
    Key1, Key2, Attr
    1, p1, a
    2, p1, b

];


// Key 1 / p1 already exists.
// This is processed as an update, as expected.
MERGE ON Key1, Key2 CONCATENATE (Target)
LOAD
    'U'  AS Operation,
    1    AS Key1,
    'p1' AS Key2,
    'x1' AS Attr
AUTOGENERATE 1;


// // Add a new row using a regular CONCATENATE.
// CONCATENATE (Target)
// LOAD
//     2    AS Key1,
//     'p1' AS Key2,
//     'b'  AS Attr
// AUTOGENERATE 1;


// Key 2 / p1 now exists in Target.
// I expected this to be processed as an update.
MERGE ON Key1, Key2 CONCATENATE (Target)
LOAD
    'U'  AS Operation,
    2    AS Key1,
    'p1' AS Key2,
    'x2' AS Attr
AUTOGENERATE 1;

[Katlitipkins]

Hey, thanks — great answer, and the counter-example makes it crystal clear. So the key index is built once and a regular CONCATENATE simply never updates it. That also means the resulting 'insert' silently creates a duplicate key pair, which is the really dangerous part.

Out of curiosity — where did you learn this? I couldn't find anything about the internal key index in the official docs. Trial and error, or some deeper source on MERGE internals?

Thanks again, accepting this as the solution.

[TheLazyDeveloper]

Learned it from trial and error and chatgpt in the past lol 

[Katlitipkins]

Thanks, that clicked!

One more finding: the index CAN be kept up to date — if the new row is added via a MERGE insert rather than a plain CONCATENATE, the next MERGE recognizes the key and processes 'U' as a proper update. So effectively: MERGE target = MERGE-only writes for the rest of the script.

But honestly… undocumented index, silent duplicate rows if you mix in one CONCATENATE, and now a whole discipline around a single table. Where are the perks? 😄