Union or Join

Hi,

I have a simple question to ask here. I can achieve the desired output but I am nor sure if I am using the correct way.

So, I want to "join" or "append" the following 2 input into the result format. I want to keep all customers but no duplicate customer IDs, using union function seems to be giving me this issue. The real data source will have multiple columns in each input. Don't know if that matters but just wanted to point out.

I just want to ask what is the most effecient and smart way to achieve this.

All helps appreciated.

Input 1			Input 2

Customer ID	Product 1		Customer ID	Product 2
1	10		2	30
2	20		3	11
3	33		7	23
6	21		8	43
7	32		9	12


Output

Customer	Product 1	Product 2
1	10	0
2	20	30
3	33	11
6	21	0
7	32	23
8	0	43
9	0	12

Join

Accepted answers

jdunkerley79

A combination of the two I think.

Join the inputs on CustomerID

Then Union all three output L/J/R from the Join together

Join and Union.yxmd

Federica_FF

That's happens because the Union after the Join is performing an union by Field name.

The ID column of the L and R input is called "Customer ID".

The ID columns of the Join are called "Customer ID" (left table) "Right_Customer ID" (right table).

So the L and R ID records are below the Customer ID column and you have the null value under the Right_Customer ID.

All comments

jdunkerley79

A combination of the two I think.

Join the inputs on CustomerID

Then Union all three output L/J/R from the Join together

Join and Union.yxmd

Federica_FF

Hi!

Both of them!

Use a Join to have a Inner Join and the connect the 3 connectors of the join (R, L and J) to the Union tool to have a full outer join.

hanswang09

Hi,

Thanks for reply. I tried this way but I got confused when I saw null customer IDs in some of the rows for the column Right_Customer ID. I guess it's because there are customers existing in input 2 but not input 1. But it seems like for the first customer ID column, it appears to be having all unique values. Just want to figure the bottom reason of it.

Thanks

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335