How to Link two table with mutliple fields

[kristeljoymalapitan]

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

[PrashantSangle]

What do you mean by OR operator???? Can explain with sample data and required output?

[kristeljoymalapitan]

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

[jfkinspari]

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]

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]

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]

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]

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

[kristeljoymalapitan]

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]

Can you share the sample data