Matching On Multiple Column Combinations

[pmelikian]

Hello,

Is it possible to join this table with a large set of data and bring back matches that have any combination of these 3 columns.

For example: if there is a line item with Cust Id 367780, Plant 4320, and Division 75, that would be a match 

[zajaccount · Accepted solution]

hI,

please see if this workflow helps:

Basically what I did was I took all the three columns separately using the select tool and appended them to one another. After that all of the combinations are created and you can join the new table with the big data set and set the join to join on these three fields.

If this helps, please accept the solution so that others can find it more easily.

[brendafos]

First, you can join on multiple fields. Does it meet your use case to create the join on all three columns?

Or if that is not your use case, you can try the Multi-Field Formula tool under Preparations category.

[BrianR]

In addition to @zajaccount, you could do this with a join tool as well. Join the two tables, the large one and the "target" table...then, you can either choose to join on one, two, or three fields...in your case, you want to join on all three fields, so configuring the Join tool to join on all three key fields (Parent ID, Plant ID, Division ID) will yield only matching rows where ALL three fields join.

[zajaccount]

Hi Brian,

would this work based on what the OP said?

For example: if there is a line item with Cust Id 367780, Plant 4320, and Division 75, that would be a match 

those three values are in a different row, yet the expected result would be to get a match - wouldn't joining on all three fields only match if the values were in one row (this is why I used the appends first, to get all the combinations).

[BrianR]

@zajaccount yes correct! I was thinking all three existed in the same row...good catch!