outer join

[mahitham]

Hi Experts,

can any one please help me on this.

I have LEFT OUTER JOIN in my sql  query  to implement in Qlik

In qlik we don't have LEFT OUTER JOIN only have outer join. Please help me on this.

Thanks in advance.

[devyanshu_gupta]

There is left outer join in qlik. Syntax used is just Left Join.

And to join you don't need any on condition. Use aliasing to give the corresponding fields same name and it will join the tables on that particular column.

For Instance

Load

ID,

WORKFLOWID,

ISSUES

From RESTISSUE;

Left Join

Load

WORKFLOW_ID as WORKFLOWID,

WORKFLOW
From RESTWORKFLOW;

Regards,

Devyanshu

[Gabriel]

Hi,

You have LEFT OUTER JOIN  in Qlik. Here is an example from Help, also read link

https://help.qlik.com/en-US/sense/June2017/Subsystems/Hub/Content/Scripting/ScriptPrefixes/Join.htm

tab1:

LOAD * from file1.csv;

tab2:

LOAD * from file2.csv;

.. .. ..

join (tab1) LOAD * from file3.csv;

Hope it helps

Gabriel

[OmarBenSalem]

You can use left join.

Ps: u can always use ur sql query directly in qlik ! If ur familiar with joins using sql, u can always keep on using them.

In fact, when u load a table in qlik:

u'll have 2 parts:

load *

select *

from

source;

In the load part, u use qlik's functions;

in the select part, u use ur db functions.

what I mean, is that u can create ur joins as u're doing in the select part; then load * without further transformations.

Hope that was clear

[YoussefBelloum]

Hi,

Since LEFT JOIN = LEFT OUTER JOIN and since you already stored your tables on QVD format

you can simply do this:

RESTISSUE:

LOAD

WORKFLOWID as WORKFLOW_ID

.

.

FROM RESTISSUE QVD;

LEFT JOIN

WORKFLOW :

LOAD

WORKFLOW_ID //(or rename this field to WORKFLOWID )

.

.

FROM WORKFLOW  QVD;

[mahitham]

Hi Youssef,

Thanks for your reply.

Instead of left join if I have linked these tow tables based on common field ie., WORKFLOWID. Is this correct?

Please note there is no common records in between RESTISSUE table WORKFLOWID and WORKFLOW table WORKFLOWID.

[YoussefBelloum · Accepted solution]

Hi,

If you don't Use a specific type of join (explicitely defined) Qlik will do the join by homonym, it will search for 2 fields with the same name, and it will do an OUTER JOIN,

example, if you do like this:

RESTISSUE:

LOAD

WORKFLOWID as WORKFLOW_ID

.

.

FROM RESTISSUE QVD;

WORKFLOW :

LOAD

WORKFLOW_ID //(or rename this field to WORKFLOWID )

.

.

FROM WORKFLOW  QVD;

==> you will have your tables joined by the field WORKFLOW_ID , and you will have the records of both tables in your model.

[OmarBenSalem]

That was very well put !

[YoussefBelloum]

Thank you bro