Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to do left outer join in qlik via script

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;

5 Replies
sunny_talwar

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;

Not applicable
Author

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.

sunny_talwar

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

hic
Former Employee
Former Employee

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;

Not applicable
Author

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'

;