Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
kristeljoymalapitan
Contributor

How to Link two table with mutliple fields

Good Day,

Please help the best approach in linking two tables (using left join) with multiple fields, please see below sample query

table1:

LOAD

cust_id,

app_id,

account_number

FROMsource....;

LEFT JOIN(table1)

LOAD

cust_id,

app_id,

reasons

RESIDENT (table2);

 

I need to link table1 and table2 using cust_id and app_id fields with OR operator

 

Thank you

 

 

Labels (2)
23 Replies

Re: How to Link two table with mutliple fields

What do you mean by OR operator???? Can explain with sample data and required output?
Great dreamer's dreams never fulfilled, they are always transcended.
kristeljoymalapitan
Contributor

Re: How to Link two table with mutliple fields

Hi,

It should be like below syntax in oracle, (AND operator instead of OR)

 

select a.app_id, b.cust_id

from table1 a

left join table2 b

on a.app_id = b.app_id AND a.cust_id = b.cust_id;

 

Column app_id and cust_id should be the identifier or key to link two tables

 

Thank you

Partner
Partner

Re: How to Link two table with mutliple fields

No need to do anything special. Just make sure both fields exist in both tables.

Qlik will join on all fields that has the same name in the two tables

anushree1
Valued Contributor II

Re: How to Link two table with mutliple fields

Please try below, as per your query i believe , you must use inner join(equi join of sql)

LOAD

cust_id as src_cust,

app_id as src_app,

autonumber(cust_id&app_id) as %key,

account_number

FROMsource....;

LEFT/Right/Inner(Based on your need if nothing is specified it will be full outer join) JOIN(table1)

LOAD

cust_id,

app_id,
autonumber(cust_id&app_id) as %key,
reasons

RESIDENT (table2);

kristeljoymalapitan
Contributor

Re: How to Link two table with mutliple fields

Hi Anushree1,

It works, however the output are all null values, I found out that cust_id and app_id in Table1 is in Varchar datatype while cust_id and app_id in Tbale2 is in number datatype, what will be the best approach to this

 

Thanks

anushree1
Valued Contributor II

Re: How to Link two table with mutliple fields

you could use autonumber(num(cust_id)&num(app_id )) as %key , to create the key field , by converting the datatype to num, if it does not work please share the sample app

kristeljoymalapitan
Contributor

Re: How to Link two table with mutliple fields

Hi Anushree1,

Please see sample app, all data are null using mentioned query you provided, I need the data to display under OWNED_BUSS column using key

 

sample app.png

kristeljoymalapitan
Contributor

Re: How to Link two table with mutliple fields

Hi Anushree1,

NUM function is not working,
How about if I will convert the datatype into character, what will be the syntax?
please advice for me to try if will work

Thank you in advance
anushree1
Valued Contributor II

Re: How to Link two table with mutliple fields

Can you share the sample data