topic Left Join Behavior in App Development

Left Join Behavior

Coldie — Fri, 10 Nov 2023 13:22:32 GMT

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.

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 ';');

Re: Left Join Behavior

marcus_sommer — Fri, 10 Nov 2023 14:19:34 GMT

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.

Re: Left Join Behavior

vincent_ardiet_ — Fri, 10 Nov 2023 14:28:11 GMT

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.