Join based on fields with the most matches

[Noorakroush]

Is there a way to join two data streams by checking first which field will give the most matches and then doing the join? 

I have two data sources, the first time I join on say CustomerID i get 79 matches, but when I match on FileID i get 92 matches, but this will not always be the case with all my reports, I want a way to check first then use the join field on either CustomerID or FileID depending on which will join more records

Thanks

[fmvizcaino · Accepted solution]

Hi @Noorakroush ,

I'm not aware of a direct way of doing that, so I'm attaching my example here.

Let me know if this works for you.

Best,

Fernando Vizcaino

[Noorakroush]

This worked perfectly! Thank you so much 

[seven]

Hi @Noorakroush 

I mocked up an example. Since you don't know which field will give more matches, you can verticalise them into one field, perform a join, test the join with the summarize tool, rejoin on the field with more joins dynamically, and pivot back to the original structure. I used a generate rows and formula tool combo to simulate a second input file in which the proportions of these fields will join and now each time you run the example, it can potentially join on one or the other field, just like you asked.

This method does not require any manual testing and you will not have to run it multiple times to test any fields.