Join Tool To Add New Column
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Labels:
- Workflow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Would that get rid of any customers who were only in the right input file though?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
You should be able to handle that by summarizing before the join.
