Qlik Community

Qlik Sense Data Connectivity

Discussion board where members can learn more about Qlik Sense Data Connectivity.

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;

Tags (2)
5 Replies

Re: How to do left outer join in qlik via script

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

Re: How to do left outer join in qlik via script

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.

Re: How to do left outer join in qlik via script

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

Employee
Employee

Re: How to do left outer join in qlik via script

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

Re: How to do left outer join in qlik via script

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'

;

Community Browser