Skip to main content

Merge

The Merge prefix can be added to any LOAD or SELECT statement in the script to specify that the loaded table should be merged into another table. It also specifies that this statement should be run in a partial reload.

The typical use case is when you load a change log and want to use this to apply inserts, updates, and deletes to an existing table.

Information noteFor partial reload to work properly, the app must be opened with data before a partial reload is triggered.

Perform a partial reload using the Reload button. For more information, see Button. You can also use the Qlik Engine JSON API.

To perform a partial reload on a schedule, click More on an app and then choose Schedule reload. In the dialog box that opens, turn on Partial reload.

Syntax:  

Merge [only] [(SequenceNoField [, SequenceNoVar])] On ListOfKeys [Concatenate [(TableName)]] (loadstatement | selectstatement)

Arguments:  

Arguments
Argument Description
only An optional qualifier denoting that the statement should be executed only during partial reloads. The statement is disregarded during normal (non-partial) reloads.
SequenceNoField The name of the field containing a timestamp or a sequence number that defines the order of the operations.
SequenceNoVar

The name of the variable that gets assigned the maximum value for SequenceNoField of the table being merged.

ListOfKeys A comma separated list of field names specifying the primary key.
Operation The first field of the load statement must contain the operation as a text string: 'Insert', 'Update', or 'Delete'. ‘i’, ‘u’ and ‘d’ are also accepted.

General functionality

During a normal (non-partial) reload, the Merge LOAD construction works as a normal Load statement but with the additional functionality of removing older obsolete records and records marked for deletion. The first field of the Load statement must hold information about the operation: Insert, Update, or Delete.

For each loaded record, the record identifier is compared with previously loaded records, and only the latest record (according to the sequence number) will be kept. If the latest record is marked with Delete, none will be kept.

Target table

Which table to modify is determined by the set of fields. If a table with the same set of fields (except the first field; the operation) already exists, this will be the relevant table to modify. Alternatively, a Concatenate prefix can be used to specify the table. If the target table is not determined, the result of the Merge LOAD construction is stored in a new table.

If the Concatenate prefix is used, the resulting table has a set of fields corresponding to the union of the existing table and the input to the merge. Hence, the target table may get more fields than the change log that is used as input to the merge.

A partial reload does the same as a full reload. One difference is that a partial reload rarely creates a new table. Unless you have used the Only clause, a target table with the same set of fields from the previous script execution always exists.

Sequence number

If the loaded change log is an accumulated log, that is, it contains changes that already have been loaded, the parameter SequenceNoVar can be used in a Where clause to limit the amount of input data. The Merge LOAD could then be made to only load records where the field SequenceNoField is greater than SequenceNoVar. Upon completion, the Merge LOAD assigns a new value to the SequenceNoVar with the maximum value seen in the SequenceNoField field.

Operations

The Merge LOAD can have fewer fields than the target table. The different operations treat missing fields differently:

Insert: Fields missing in the Merge LOAD, but existing in the target table, get a NULL in the target table.

Delete: Missing fields do not affect the result. The relevant records are deleted anyway.

Update: Fields listed in the Merge LOAD are updated in the target table. Missing fields are not changed. This means that the two following statements are not identical:

  • Merge on Key Concatenate Load 'U' as Operation, Key, F1, Null() as F2 From ...;
  • Merge on Key Concatenate Load 'U' as Operation, Key, F1 From ...;

The first statement updates the listed records and changes F2 to NULL. The second does not change F2, but instead, leaves the values in the target table.