Script : sum after join with a distinct clause give an incorrect result

[simonaubert]

Hello all,

I spent my evening on this issue :  
Here my small script to reproduce. Basically, I have a table, I join with another table with a left. No cartesian product, everything seems easy.

TOTO:
load
1 as header_id,
1 as titi,
1 as qte_produit
Autogenerate(1);
Concatenate
LOAD
1 as header_id,
1 as titi,
3 as qte_produit
Autogenerate(1);
Concatenate
LOAD
1 as header_id,
1 as titi,
3 as qte_produit
Autogenerate(1);

NoConcatenate
totoche:
LOAD * RESIDENT TOTO;

left join(totoche)
LOAD distinct
1 as titi,
1 as tutu
Autogenerate(1);

drop field titi;

Drop table TOTO;

NoConcatenate
Bidule :
load header_id, sum(qte_produit) as rr RESIDENT totoche group by header_id;

drop table totoche;

Exit script;

The expected result is 7. The given result is 4.

Can someone explain please ?

Qlik Sense May 2022 Patch 6.

Best regards,

Simon

[vinieme12]

Refer this article, remove load distinct

http://www.qlikfix.com/2013/07/30/distinct-can-be-deceiving/

left join(totoche)
LOAD distinct
1 as titi,
1 as tutu
Autogenerate(1);

[Vegar · Accepted solution]

You expect this output for your totoche table.

header_id, titi, qte_produit, tutu

1, 1, 3, 1

1, 1, 3, 1

1, 1, 1, 1

But as @vinieme12 pointed out, you where using distinct. Distinct will be applied on the whole output of your join and will therefore only include the 1,1,3,1 row once. Therefore the output will be.

header_id, titi, qte_produit, tutu

1, 1, 3, 1

1, 1, 1, 1

And the sum of qte_produit will be 4 not 7.

[simonaubert]

Hello @vinieme12  and thanks . In real life, I had duplicate in this step. And the workaround I found is to load a temp table with the distinct and then do the join. What I was looking for was why the hell it does not work.

Best regards,

Simon

[simonaubert]

Hello @Vegar . Thanks, that was the explanation I was looking for

" Distinct will be applied on the whole output of your join "

Damned, this is so unintuitive !

Best regards,

Simon