Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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
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
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
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;
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.
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.
That was very well put !
Thank you bro