Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Coldie
Contributor
Contributor

Left Join Behavior

The below script should produce a total sum of -770 000. However, it only does so in TableB and TableC.

The only difference being that TableB uses a unique key (which is dropped after load) and TableC uses a non distinct load in the right table.

Can anybody explain why one of the duplicate values is removed in the load statement of TableA, while it isn't removed in TableB nor in TableC? QV and QS both behave the same.

 Coldie_0-1699622338495.png

TIA

Load Statements:

TableA:
Load * Inline [
KeyA;ValueA
K300;30000
K300;-800000
K300;-800000
K300;800000
] (delimiter is ';');

LEFT JOIN (TableA)
Load DISTINCT * Inline [
KeyA
K300
] (delimiter is ';');


TableB:
NoConcatenate
Load * Inline [
KeyB;ValueB;UniqueKey
K300;30000,1
K300;-800000,2
K300;800000,3
K300;-800000,4
] (delimiter is ';');


LEFT JOIN (TableB)
Load DISTINCT * Inline [
KeyB
K300
] (delimiter is ';');

Drop Field UniqueKey;


TableC:
NoConcatenate
Load * Inline [
KeyC;ValueC
K300;30000
K300;-800000
K300;800000
K300;-800000
] (delimiter is ';');

LEFT JOIN (TableC)
Load * Inline [
KeyC
K300
] (delimiter is ';');

Labels (1)
1 Solution

Accepted Solutions
marcus_sommer

The join to TableA is a distinct load and this statement worked against both loads and removes therefore the records.

Beside this what's the aim behind this testing? In most scenarios it would be more suitable to concatenate such tables as keeping them separate. 

View solution in original post

2 Replies
marcus_sommer

The join to TableA is a distinct load and this statement worked against both loads and removes therefore the records.

Beside this what's the aim behind this testing? In most scenarios it would be more suitable to concatenate such tables as keeping them separate. 

vincent_ardiet_
Specialist
Specialist

Qlik is sometimes doing some jokes.
When you are doing this:
LEFT JOIN (TableB)
Load DISTINCT * Inline [
KeyB
K300
] (delimiter is ';');

Qlik is doing a DISTINCT on both side of the join, so on TableB and on your InLine.