Join or concatenate a distinct table with a non-distinct table results in distinct table

[ToniKautto]

Join or concatenate a distinct table with a non-distinct table results in a distinct table, while the expected result would be a non-distinct table.

For example, the below script is expected to result in a table with three rows, two from the non-distinct load and one from the distinct load. The resulting table will instead be distinct and only have one row.

T1:
LOAD * Inline [
F1
a
a
];

Concatenate (T1)
LOAD Distinct * Inline [
F1
a
a
];

Resolution:

The entire resulting table from a join or concatenate will be distinct when the DISTINCT prefix is used in any of the loads.

The solution to accomplish a join or concatenation of a distinct and a non-distinct table is to load the two tables separately and then join or concatenate them.

For example the below script will result in a table with three rows:

T1:
LOAD * Inline [
F1
a
a
];

T2:
NoConcatenate
LOAD Distinct * Inline [
F1
a
a
];

Concatenate(T1)
LOAD 
F1
Resident T2;

Drop Tables T2;

Join Use Case

Another scenario that can be affected with this behaviour could be when using Join clauses, in this example, we have a LEFT JOIN to preserve all the rows on the table to the left (T1) that has 2 rows, normally this would be written like this:

T1:
LOAD * Inline [
F1
a
a
];
LEFT JOIN
T2:
LOAD Distinct * Inline [
F1
a
a
];

This will result again in only one row result due that the Distinct in the second table is applied to the result set.

As a workaround this can be changed to:

T2:
LOAD Distinct * Inline [
F1
a
a
];

T1:
NoConcatenate
LOAD * Inline [
F1
a
a
];
LEFT JOIN
LOAD * Resident T2;
Drop Tables T2

Note: The additional table must be dropped when no longer needed to avoid synthetic keys.

Environment:

QlikView 
Qlik Sense Enterprise on Windows 
Qlik Cloud 
Qlik Sense Business