Qlik Community

Knowledge

Search or browse our knowledge base to find answers to your questions ranging from account questions to troubleshooting error messages. The content is curated and updated by our global Support team

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

cancel
Showing results for 
Search instead for 
Did you mean: 
ToniKautto
Employee
Employee

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

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 

Labels (1)
Version history
Last update:
2 weeks ago
Updated by: