join then union creates null-how to merge?

[utsarunner981]

hi everyone! i am trying to see if this is possible. can a union come after a join without the nulls? this is what i am trying to accomplish: 

dataset1: 

Product ID	name	price
1001	table	$ 100.00
1002	sofa	$ 250.00
1003	chair	$   40.00
1004	loveseat	$ 150.00
1005	bed	$ 300.00
1006	mattress	$ 100.00

dataset2:

Product ID	Date Sold
1001	10/31/2021
1004	11/3/2021
1006	11/1/2021

dataset3:

name	shipping
table	california
sofa	oklahoma
chair	texas
loveseat	california
bed	texas
mattress	texas

desired output:

Product ID	name	price	Date Sold	shipping
1001	table	$100	10/31/2021	california
1002	sofa	$250		oklahoma
1003	chair	$40		texas
1004	loveseat	$150	11/3/2021	california
1005	bed	$300		texas
1006	mattress	$100	11/1/2021	texas

sample workflow: 

 

[afv2688]

Hello @utsarunner981 ,

Could you please explain with a result window what you are trying to accomplish? You are showing us 3 different dates for 3 product ID, what would be the expected value to be seen for the rest of the cells?

Regards

[utsarunner981]

Hello @afv2688, 

Yes, so I have 3 datasets-first two are joined by their ProductID- the third is unioned by name. The last dataset is the desired output and the expected value for blank cells can stay as is. 

[afv2688 · Accepted solution]

Hello @utsarunner981 ,

If you union the third dataset directly you won't have the data joined together.

Maybe what you are looking for is to do 2 different joins on this specific case

Regards

[utsarunner981]

@afv2688  yes! this solution worked! thank you!