How to Implement OR condition of Join in Qlikview

[sarfaraz_sheikh]

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

[jonathandienst]

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

[tresesco]

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;

[sarfaraz_sheikh]

can you keep any example here for my understanding related with Qlik Load.

[tresesco]

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.

[petter]

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.

[petter]

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;

[tresesco]

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.