Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
jduluc12
Creator
Creator

Join two tables on multiple columns in the where clause

Hi,

I got two tables.

Table 1:

ItemNumDescAmount
Item156Item_1 desc100
Item298Item_2 desc500
Item380Item_3 desc900
    

 

Table 2:

ItemNumDescAmountID
Item_156Desc_do_not_match251
Item_289Item_2 desc892
Item_3100Desc_do_not_match4503
Item_1456Desc_do_not_match1234
Item_2145Desc_do_not_match985
Item_3165Desc_do_not_match456

 

I want to do something like this

left join (Table1)

load Item

resident Table2

where Table1.Num = Table2.Num or Table1.Desc = Table.Desc or Table1.Amount = Table2.Amount * 2;

to have an output like

ItemNumDescAmountID
Item156Item_1 desc1001
Item298Item_2 desc5002
Item380Item_3 desc9003

 

Item1 matches because of Table1.Num = Table2.Num.

Item2  matches because of Table1.Desc = Table2.Desc.

Item3 matches because of Table1.Amount = Table2.Amount * 2.

The above query does not work because it does not recognize the Table1's fields in the where clause

Labels (1)
1 Solution

Accepted Solutions
Saravanan_Desingh

Try this,

QUALIFY *;

Table1:
LOAD * INLINE [
    Item, Num, Desc, Amount
    Item1, 56, Item_1 desc, 100
    Item2, 98, Item_2 desc, 500
    Item3, 80, Item_3 desc, 900
];

Join(Table1)

Table2:
LOAD * INLINE [
    Item, Num, Desc, Amount, ID
    Item1, 56, Desc_do_not_match, 25, 1
    Item2, 89, Item_2 desc, 89, 2
    Item3, 100, Desc_do_not_match, 450, 3
    Item1, 456, Desc_do_not_match, 123, 4
    Item2, 145, Desc_do_not_match, 98, 5
    Item3, 165, Desc_do_not_match, 45, 6
];

UNQUALIFY *;

Table3:
NoConcatenate
LOAD *
Resident Table1
Where  Table1.Num = Table2.Num 
	Or Table1.Desc = Table2.Desc 
	Or Table1.Amount = Table2.Amount * 2;
	
Drop Table Table1;

View solution in original post

3 Replies
Saravanan_Desingh

Try this,

QUALIFY *;

Table1:
LOAD * INLINE [
    Item, Num, Desc, Amount
    Item1, 56, Item_1 desc, 100
    Item2, 98, Item_2 desc, 500
    Item3, 80, Item_3 desc, 900
];

Join(Table1)

Table2:
LOAD * INLINE [
    Item, Num, Desc, Amount, ID
    Item1, 56, Desc_do_not_match, 25, 1
    Item2, 89, Item_2 desc, 89, 2
    Item3, 100, Desc_do_not_match, 450, 3
    Item1, 456, Desc_do_not_match, 123, 4
    Item2, 145, Desc_do_not_match, 98, 5
    Item3, 165, Desc_do_not_match, 45, 6
];

UNQUALIFY *;

Table3:
NoConcatenate
LOAD *
Resident Table1
Where  Table1.Num = Table2.Num 
	Or Table1.Desc = Table2.Desc 
	Or Table1.Amount = Table2.Amount * 2;
	
Drop Table Table1;
Saravanan_Desingh

Output:

commQV49.PNG

jduluc12
Creator
Creator
Author

I was thinking along the same lines.

Thanks for confirming it.