topic joining two table with a OR condition in App Development

joining two table with a OR condition

Kalkumar — Wed, 07 Feb 2024 10:19:35 GMT

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

Re: joining two table with a OR condition

LRuCelver — Wed, 07 Feb 2024 11:12:19 GMT

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;

Re: joining two table with a OR condition

Anil_Babu_Samineni — Wed, 07 Feb 2024 11:21:58 GMT

@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

Re: joining two table with a OR condition

Nagaraju_KCS — Wed, 07 Feb 2024 12:47:30 GMT

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

Re: joining two table with a OR condition

Chanty4u — Wed, 07 Feb 2024 16:23:21 GMT

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);

Re: joining two table with a OR condition

Kalkumar — Sun, 25 Feb 2024 11:08:55 GMT

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?