Joining data based on multiple criteria

[lmaughan]

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!

[bpatel]

hi @lmaughan ,

you can use the join to accomplish this!

hope this helps!

[kumaranvr1984]

Hi,

Use multi join tool and  use join by specific fields option.

Regards,

Kumaran

[Blake · Accepted solution]

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! 

[grazitti_sapna · Accepted solution]

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.

[lmaughan]

Thank you for your help.  I found that my data needed additional cleansing and then it worked great.  I appreciate your help!

[lmaughan]

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!

[lmaughan]

I had not tried the multijoin tool.. thanks for the suggestion!