Joining 2 Datasets but Multiple Columns to Compare

Question

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 NumberAP TerritoryAP LEAR TerritoryAR LE CodeAR LEDate Number

Example source Data 1:

Invoice NumberAP TerritoryAP LEAR TerritoryAR LE CodeAR LEDate NumberDXB12345678USUS LEUAE  8/31/20221,000.00DXB12345679USUS LE ABCAustralia LE9/1/20221,000.00DXB12345680USUS LE  Australia LE9/2/20221,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 TerritoryAR LE codeAR LEUAEAAAUAE LEAustraliaABCAustralia LE

danilang · Accepted Answer

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