Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
i'm trying to do a left outer join from table a to table b.
but i get always an error.
My script:
Load *;
sql select cod_contenitore as contenitore_join
from dir_documenti
where dir_documenti.CLIENTE_1 = '04011' AND
dir_documenti.COD_CLASSE_1 = 'QAV';
left join Load *;
sql select cod_contenitore as contenitore_join
from dir_acquisizione
where dir_acquisizione.CLIENTE_1 = '04011' AND
dir_acquisizione.COD_CLASSE_1 = 'QAV'
and join_reference is null;
Maybe the missing table names is causing an issue. Try this:
Table:
Load *;
sql select cod_contenitore as contenitore_join
from dir_documenti
where dir_documenti.CLIENTE_1 = '04011' AND
dir_documenti.COD_CLASSE_1 = 'QAV';
Left Join (Table)
Load *;
sql select cod_contenitore as contenitore_join
from dir_acquisizione
where dir_acquisizione.CLIENTE_1 = '04011' AND
dir_acquisizione.COD_CLASSE_1 = 'QAV'
and join_reference is null;
I think now the join is working, but my purpose is to take every record from table dir_documenti that do not have match on dir_acquisizione.
in a normal left join i do this by setting in WHERE
dir_acquisizione.cod_contenitore is null.
i can do the same here or i have to use a specific instruction of Qlik?
Thank for your help mate.
my purpose is to take every record from table dir_documenti that do not have match on dir_acquisizione.
in a normal left join i do this by setting in WHERE
dir_acquisizione.cod_contenitore is null.
Not sure I completely understand what you are trying to do. Would you be able to elaborate.
Best,
Sunny
The best way is probably to not use a join, but instead use the Exists() function:
Acq:
sql select cod_contenitore as contenitore_join_in_ dir_acquisizione
from dir_acquisizione where ... ;
Doc:
Load * Where not Exists(contenitore_join_in_ dir_acquisizione, cod_contenitore);
sql select cod_contenitore, ...
from dir_documenti where ... ;
Drop Table Acq;
Hi,
Try joining the sql tables directly like this
Load *;
sql select cod_contenitore as contenitore_join
from dir_documenti left outer join dir_acquisizione on (dir_documenti .cod_contenitore=dir_acquisizione.cod_contenitore)
where
dir_documenti.CLIENTE_1 = '04011' AND
dir_documenti.COD_CLASSE_1 = 'QAV' AND
dir_acquisizione.CLIENTE_1 = '04011' AND
dir_acquisizione.COD_CLASSE_1 = 'QAV'
;