Join Tool To Add New Column

I have 2 identical reports, one from June and one from July. I am trying to use the join tool to add the revenue column from the June file to the end of the July file. I want the output file to have a full list of all customers in one column (even if they were only a customer in one of the months and not both), another column with the revenue from July for each customer and a third column with the revenue from June for each customer. How should I configure the join tool to do this? Thank you

Workflow

Accepted answers

All comments

cjaneczko

One way is to add the join tool and join on the Customer Number. Bring in the the Revenue from both sheets. After that add a Union Tool and join in the Left, Right and Center of the join. This will bring every record in from both sheets whether there was a match or not. You should then have a column for Customer Number, June Rev and July Rev.

giannademetroulakos

This is what I currently have set up, but the output has a Customer Name column and a Right Customer Name column. I am looking for one column that has all the customer names from both the right and left inputs

Rags1982

Add the select AFTER the Right of the join but BEFORE the Union. Then Rename 'Right_Customer' to Customer. The Union will then work as intended.

jdminton

The only one labeled as "Right_Customer" would be from the "J" output. The right and left will just be labeled "Customer". You can just deselect the right_customer in the join itself.

giannademetroulakos

I have realized a new problem. The customer name may be in the july file on 2 separate lines but each line has a different revenue value. I want those values to either be added together in the output and have one single line per customer or have the output have the 2 lines for the customer and keep the correct revenue values - right now my output file is giving me 2 lines for the customer but is picking one revenue value and using it for both lines

jdminton

You should be able to handle that by summarizing before the join.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703