Join based on fields with the most matches

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

Join

Accepted answers

fmvizcaino

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

2020-07-08 Join based on fields with the most matches.yxmd

All comments

fmvizcaino

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

2020-07-08 Join based on fields with the most matches.yxmd

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.

join_on_more_populated_field.yxmd

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167