Joining 2 Datasets but Multiple Columns to Compare

[andreahq]

Hello,

I have an invoice data set where I want to fill the data for multiple columns (AR Territory, AR LE, and AR LE Code) based on a mapping table. The problem is that there isn't one single column I can use in the Join tool. I'm trying to see if there is a solution that would not require multiple join tools specially since the data set is large. Thanks

Desired output columns filled:

Invoice Number

AP Territory

AP LE

AR Territory

AR LE Code

AR LE

Date

Number

Example source Data 1:

Invoice Number	AP Territory	AP LE	AR Territory	AR LE Code	AR LE	Date	Number
DXB12345678	US	US LE	UAE			8/31/2022	1,000.00
DXB12345679	US	US LE		ABC	Australia LE	9/1/2022	1,000.00
DXB12345680	US	US LE			Australia LE	9/2/2022	1,000.00

Note: I always have either the AR territory or AR LE columns but never both. Sometimes AR LE code is available 

Example source Data 2 (AR Territory to LE Mapping File)

AR Territory	AR LE code	AR LE
UAE	AAA	UAE LE
Australia	ABC	Australia LE

[danilang · Accepted solution]

Hi @andreahq 

EDIT:  Missed the requirement to not have multiple Join tools.  There may be a way by transposing your data and mapping files, joining on Name and Value and then Cross Tabbing.   This will not be any more simple nor will it run any faster. 

But since your mapping file is bi-directional and unique in both directions you can split you data file based on the presence or absence of data in the AR Territory field, IsEmpty([AR Territory]).  Map the AR LE code and AR LE for records the have a AR Territory and Map the AR Territory and AR LE code for the rest

Dan