Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mahitham
Contributor

outer join

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.

1 Solution

Accepted Solutions
YoussefBelloum
Esteemed Contributor

Re: outer join

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.

8 Replies
devyanshu_gupta
New Contributor III

Re: outer join

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
Valued Contributor II

Re: outer join

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
Esteemed Contributor

Re: outer join

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
Esteemed Contributor

Re: outer join

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
Contributor

Re: outer join

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
Esteemed Contributor

Re: outer join

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
Esteemed Contributor

Re: outer join

That was very well put !

YoussefBelloum
Esteemed Contributor

Re: outer join

Thank you bro

Community Browser