Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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);
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.
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
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