Joining data based on multiple criteria
- 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 am trying to join datasets based on multiple criteria.
Company A and Company B lines must match in both files. First dataset contains sales information.
Company A Name | Company A ID | Company B Name | Company B ID | Sales |
CompA | 1234 | CompB | 8654 | 10,000.00 |
Second dataset contains contract number.
Company A Name | Company A ID | Company B Name | Company B ID | Contract # |
CompA | 1234 | CompB | 8654 | WGT-5402 |
I would like the end result to look like this.
Company A Name | Company A ID | Company B Name | Company B ID | Contract # | Sales |
CompA | 1234 | CompB | 8654 | WGT-5402 | 10,000.00 |
Need help on the right approach to take with this transformation.
Thanks in advance!
Solved! Go to Solution.
- 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
Hi,
Use multi join tool and use join by specific fields option.
Regards,
Kumaran
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @lmaughan - You can join on as many fields are you need to in order to make sure you're getting the results you desire. I think in this case you want to make sure that Company A ties to Company A and Company B to Company B for both dimensions (name and ID). You can choose those fields in the configuration panel of the join tool (yellow box in screenshot) and deselect any duplicates in the embedded select tool (red box in screenshot).
See the screenshot below and attached workflow for more information.
Thanks and good luck!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @lmaughan , you can use normal join tool if you have two datasets and for N number of datasets you can use multiple join tool. Also there is no restriction for joining data on multiple criteria as in your case you want to join data on basis of company A and company B, but one thing need to be kept in mind that all the fields that you are using for joining the data must have same datatype else it will throw an error.Please refer to the screenshot for better understanding.
I hope this helps.
Thanks.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you for your help. I found that my data needed additional cleansing and then it worked great. I appreciate your help!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you -- very timely and I appreciate the explanation.
I needed to clean the dataset I am working with, but then it worked great. Thanks for the tips!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I had not tried the multijoin tool.. thanks for the suggestion!
