Left join: Getting columns of left table only if key not present in the right table

[sagarghimire]

Trying to dynamically get just the columns from the left table when doing left join if there is no 'key' columns match to the right column. i.e. do not append right table columns which would all show null except the key (assuming it is not null)

TIA

[FrederikE]

Hey @sagarghimire,

Can you describe in more detail what your problem is?

Because from what I understand, you just want to have the left columns when doing an unmatched Join (Left Join). But this is already the way Alteryx does it by default.

[sagarghimire]

Logic I am trying to implement:

If there is a match between Key1 and Key2 and date is between start and end date on lookup table return any one row ( there can be multiple records that may match) that matched.
Only if there is no match on Key 1 and Key 2 combo, join on key 3 (it is just one same value in main table for all records), if there is a match return any one row that matched.

Result desired:

Key1	Key2	date	key3	ID	start dt	end dt	key3	number	number id
1	a	1/1/2021	e	a1	12/12/2020	1/5/2022	a	1234	10
2	b	1/2/2021	e	a2	null	null	null	null	null
2	c	1/3/2021	e	a3	1/1/2021	1/5/2022	c	3456	30
4	d	1/4/2021	e	a4	1/1/2021	1/2/2021	e	897	50

Main

Key1	Key2	date	key3	ID
1	a	1/1/2021	e	a1
2	b	1/2/2021	e	a2
2	c	1/3/2021	e	a3
4	d	1/4/2021	e	a4

Look up

Key1	Key2	start dt	end dt	key3	number	number id
1	a	12/12/2020	1/5/2022	a	1234	10
1	a	12/15/2021	1/1/2021	b	1234	20
2	c	1/1/2021	1/5/2022	c	3456	30
4	d	12/20/2020	12/20/2023	d	567	40
4	d	1/1/2021	1/2/2021	e	897	50

[Prometheus]

@sagarghimire The first thing I did was turn the dates into actual dates using DateTimeParse and changing the data type in the following Join tools. I joined the tables on key 1 and key 2 in the first Join tool. Anything that wasn't joined was joined again with the lookup table on key 3. I filtered the records that were joined at the first Join tool to only allow records where date was between start dt and end dt. Then I unioned them the outputs of the Filter tool and the second Join tool and sorted on key 1 to match your expected results.

 

 

 

 

 

[sagarghimire]

This is great. Apologies, I missed one important scenario in my original example. If I were to update the second record of look up table start date as follows for example, I would get 5 records in my final output versus just four. Need to just keep one row that fulfils the in between condition out of the two records(could be multiple in real case) that matched. 

1

a

1/1/2021

1/1/2021

b

1234

20

[Prometheus]

@sagarghimire I added a Unique tool between the first Join (J anchor) and the Filter tool and checked the boxes next to key 1 and key 2.