Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Folks,
I have the below SQL code and i want it to be implement in script of QlikView
select RL.RoleName , L.location
from STP_LMS_Lead L
RIGHT OUTER JOIN
STP_LMS_Roles RL
ON (L.ChannelID = RL.RoleID or L.Leadaddedchannel= RL.RoleID)
where
L.RenewalDone = 'BK' and L.ProductId <>16;
My main concern is how i can implement or condition of join here in script.
Help would be much appreciated.
jagan , gwassenaar , hic please comment..
Sarfaraz
Why do you want to do the join in script if the SQL join already works? Where will you be loading the data from? You could do it like:
Data:
SQL SELECT ChannelID As RoleID,
Location
FROM STP_LMS_Lead
WHERE L.RenewalDone = 'BK' and L.ProductId <>16;
Concatenate(Data)
SQL SELECT Leadaddedchannel As RoleID,
Location
FROM STP_LMS_Lead
WHERE L.RenewalDone = 'BK' and L.ProductId <>16;
Join (Data)
SQL SELECT RoleName,
RoleID
FROM STP_LMS_Roles
In a single join statement using Qlik Load you might not be able to achieve that; two join statements would be required. However, if you are fetching the data from SQL supported database, you can directly use the same statement in qv script like:
Load
*;
SQL
select RL.RoleName , L.location
from <Yourdatabase reference>
RIGHT OUTER JOIN
STP_LMS_Roles RL
ON (L.ChannelID = RL.RoleID or L.Leadaddedchannel= RL.RoleID)
where
L.RenewalDone = 'BK' and L.ProductId <>16;
can you keep any example here for my understanding related with Qlik Load.
Try like:
Load
L.location,
L.ChannelID as Key
from STP_LMS_Lead L;
Right Join
Load
* ,
RL.RoleID as Key
from STP_LMS_Roles RL where L.RenewalDone = 'BK' and L.ProductId <>16;
Concatenate
Load
L.location,
L.Leadaddedchannel as Key,
1 as Dummy
from STP_LMS_Lead L;
Right Join
Load
* ,
RL.RoleID as Key
from STP_LMS_Roles RL where L.ChannelID <> RL.RoleID and L.RenewalDone = 'BK' and L.ProductId <>16;
Drop field Dummy;
Note: This script only takes care of your Right Join. To handle Right Outer Join, you might have to use another join statement.
Just a clarification - RIGHT JOIN is the same as RIGHT OUTER JOIN and the same goes for LEFT.
In some SQL dialects the OUTER with RIGHT and LEFT is optional whereas in QlikView it is not allowed to use with RIGHT or LEFT but it is implied.
OUTER in Qlik means FULL OUTER but the FULL is implied and not allowed to be used.
You can achieve the same in QlikView with one JOIN and one CONCATENATE like this example shows:
L:
LOAD * INLINE [
location, RenewalDone, ProductID, ChannelID, Leadaddedchannel
A, 'BK', 3, 1
B, 'BK', 3, 2, 22
C, 'BK', 4, 6
C, 'AK', 5, 6
D, 'BK', 3, 45, 455
E, 'BK', 3, 6, 66
F, 'BK', 16, 9, 99
];
RL:
LOAD * INLINE [
RoleID, RoleName
1, One
2, Two
3, Three
4, Four
5, Five
6, Six
9, Nine
22, Twenty-two
45, Forty-five
455, Four-hundred-fifty-five
];
RESULT:
LOAD
location,
ChannelID as RoleID,
RenewalDone,
ProductID
RESIDENT
L
WHERE
RenewalDone = 'BK' AND ProductID <> 16;
CONCATENATE
LOAD
location,
Leadaddedchannel as RoleID,
RenewalDone,
ProductID
RESIDENT
L
WHERE
RenewalDone = 'BK' AND ProductID <> 16
AND
Leadaddedchannel <> '';
LEFT JOIN
LOAD
*
RESIDENT
RL;
DROP TABLE L, RL;
Thanks Petter. It seems that I am doing too much of Qlik now-a-days. . Full Outer join was there in my mind and I wrote it for simple outer join.