topic Script : sum after join with a distinct clause give an incorrect result in App Development

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

simonaubert — Mon, 28 Nov 2022 21:14:14 GMT

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

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

vinieme12 — Tue, 29 Nov 2022 02:59:25 GMT

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

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

Vegar — Tue, 29 Nov 2022 05:59:00 GMT

You expect this output for your totoche table.

header_id, titi, qte_produit, tutu

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.

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

simonaubert — Tue, 29 Nov 2022 06:18:51 GMT

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

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

simonaubert — Tue, 29 Nov 2022 06:20:34 GMT

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