topic Re: How to do left outer join in qlik via script in Connectivity & Data Prep

How to do left outer join in qlik via script

Thu, 09 Jul 2015 15:45:47 GMT

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;

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

sunny_talwar — Thu, 09 Jul 2015 16:19:29 GMT

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;

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

Fri, 10 Jul 2015 06:07:44 GMT

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

sunny_talwar — Fri, 10 Jul 2015 09:52:17 GMT

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

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

hic — Fri, 10 Jul 2015 10:00:09 GMT

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;

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

Fri, 10 Jul 2015 10:57:53 GMT

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'

;