Forum Discussion
Join on multiple columns only if column is not null
Hi,
I have 2 tables TABLEA and DESC. I have 4 same columns on both tables Carrier, Group, Mode and State. I only want to join only if there is value in any of the 4 columns. So the final result I am looking for is only 2 results since they are the only ones that partially or fully match:
Verizon A Ship NY 10 abcde (since Ship and NY in DESC matches Ship and NY in TABLEA)
ATT B Air CA 20 efghi (since all columns match both tables)
you can't join using more than 1 active column, you can use Merge Queries to join on multiple columns but how do I tell it to only join when there is a value, but ignore join if there is no value?
thanks!
3 Replies
- lbendlin
Super User
Use Table.AddColumn with a custom columngenerator function that can implement all kinds of weird and wacky join conditions.
ryan_mayuthis is the table visual. It's the same as your raw data? What's the expected output?
Hi ryan_mayu , it is the same as raw data. The expected output is to see 2 rows if I do select * from TABLEA and select Description from DESC where
TABLEA Carrier = DESC Carrier (only if Carrier its not null) and
TABLEA Group = DESC Group (only if Group is not null) and
TABLEA Mode = DESC Mode (only if Mode is not null) and
TABLEA State = DESC State (only if State is not null)so these are the only 2 records I want to see:
Verizon A Ship NY 10 abcde (since Ship and NY in DESC matches Ship and NY in TABLEA)
ATT B Air CA 20 efghi (since all columns match both tables)
Thanks!
