joining two table with a OR condition

[Kalkumar]

Hello All, 

I wanted to achieve MS SQL server like join condition with the OR clause in Qliksense. 

Basically, i want to full outer join two tables either with EmployeeID or EmailAddress. Meaning if EmployeeID is missing or null then join with EmailAddress and vise versa. 

here is an example in SQL server.

How can i achieve this in Qlik ?

on s.EmployeeID = f.EmployeeID or s.EmailAddress = f.EmailAddress

Select s.EmployeeID,s.EmailAddress,s.Module,s.Name,
f.EmployeeID as FinanceEmpid, f.EmailAddress as FinanceEMailID, f.Module as FinanceModule , f.name as Financename
 from
sales as s 
full outer join
finance as f
on s.EmployeeID = f.EmployeeID or s.EmailAddress = f.EmailAddress

[LRuCelver]

You can join twice - once for each key - and then reload the table to combine the joined fields.

Employees:
NoConcatenate Load 
	RecNo() as EmployeeID,
    RecNo() & '@mail.com' as EmailAddress
AutoGenerate 10;

Data:
NoConcatenate Load
    If(Mod(EmployeeID, 2) = 1, EmployeeID) as EmployeeID,
    If(Mod(EmployeeID, 2) = 0, EmailAddress) as EmailAddress,
    If(Mod(EmployeeID, 2) = 1, 'ID', 'Email') as JoinedFrom
Resident Employees;



Join(Employees) Load Distinct
	EmployeeID,
    JoinedFrom as JoinedFromID
Resident Data;

Join(Employees) Load Distinct
	EmailAddress,
    JoinedFrom as JoinedFromEmail
Resident Data;

Rename Table Employees to Employees_Old;

Employees:
NoConcatenate Load
	EmployeeID,
    EmailAddress,
    If(IsNull(JoinedFromID), JoinedFromEmail, JoinedFromID) as JoinedFrom
Resident Employees_Old
Where not IsNull(EmployeeID);

Drop Tables Employees_Old, Data;

[Anil_Babu_Samineni]

@Kalkumar In general, I won't encourage joining the same table in multiple times due to permance stability. Instead, I would strongly advice to write the same query in Qlik and just load as below?

LIB CONNECT TO 'Your connection name in Qlik sense against Database';

SQL Select s.EmployeeID,s.EmailAddress,s.Module,s.Name,
f.EmployeeID as FinanceEmpid, f.EmailAddress as FinanceEMailID, f.Module as FinanceModule , f.name as Financename
 from
sales as s 
full outer join
finance as f
on s.EmployeeID = f.EmployeeID or s.EmailAddress = f.EmailAddress

[qv_testing]

I hope this will work

Sales:
Load EmployeeID,
     EmailAddress,
     Module,
     Name,
     if(LEN(TRIM(EmployeeID))=0, EmailAddress, EmployeeID) as %Key
from sales
JOIN 
Finance:
Load EmployeeID as FinanceEmpid, 
     EmailAddress as FinanceEMailID, 
     Module as FinanceModule, 
     name as Financename,
     if(LEN(TRIM(EmployeeID))=0, EmailAddress, EmployeeID) as %Key
from finance

[Chanty4u]

Try this

Table:

LOAD * INLINE [

    EmployeeID, EmailAddress, Module, Name

    1, john@example.com, Sales, John

    2, jane@example.com, Sales, Jane

];

Left Join (Table)

LOAD * INLINE [

    EmployeeID, EmailAddress, Module, Name

    1, jane@example.com, Finance, Jane

    2, sam@example.com, Finance, Sam

];

Result:

LOAD

    s.EmployeeID as Sales_EmployeeID,

    s.EmailAddress as Sales_EmailAddress,

    s.Module as Sales_Module,

    s.Name as Sales_Name,

    f.EmployeeID as Finance_EmployeeID,

    f.EmailAddress as Finance_EmailAddress,

    f.Module as Finance_Module,

    f.Name as Finance_Name

RESIDENT Table

WHERE Match(s.EmployeeID, f.EmployeeID) or Match(s.Email

Address, f.EmailAddress);

[Kalkumar]

All,  am following an approach of link able to achieve this.
This link table will be resident of Finance and Sales table and will have the columns upon which I ideally wanted to joined the these table in SQL server. 

It is working as expected. 

Do you see any cons with this approach?