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 | |||
Solved! Go to Solution.
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.
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
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.
Thank you very much for the explaination. I think that makes perfect sense.
Thanks