Join on All Matching Fields

Hello! I am trying to create an automated process based on dynamic inputs. Hoping someone can help.

Basically I need to take a data set and assign each row a 'SegmentNumber'. This number is dependent on criteria spelled out in several separate xlsx documents (one file for each SegmentNumber). These criteria files could be 1-10 fields wide, but the data set will have >100 fields. I want to be able to execute an inner joins (one for each critera doc back to the dataset) on all fields present in both files without having to manually select the fields.

Attached is an example. If anyone has any thoughts, let me know!

Example_SegmetationIdentifier.yxmd

Join

Accepted answers

JeffF

Hi @KS_dup_175

There might be a better way to do this, but here is a package with a couple of macros that join on all fields that are present in both input streams.

It is currently configured to only join on fields that have the same Name and Type. NOTE: In this case a String is not the same as a V_String.

You can remove the "Type" requirement by modifying the Join Tool in Macro1.yxmc.

The L, J, and, R output are the join outputs, The F output is a list of the fields that were used to join.

I hope this helps.

JoinMatchingFields.yxzp

All comments

JeffF

Hi @KS_dup_175

There might be a better way to do this, but here is a package with a couple of macros that join on all fields that are present in both input streams.

It is currently configured to only join on fields that have the same Name and Type. NOTE: In this case a String is not the same as a V_String.

You can remove the "Type" requirement by modifying the Join Tool in Macro1.yxmc.

The L, J, and, R output are the join outputs, The F output is a list of the fields that were used to join.

I hope this helps.

JoinMatchingFields.yxzp

terry10

@KS_dup_175 ,

You posted this answer 5 years ago and there wasn't a single comment. But JoinMatchingFields was very helpful for me! I have ~20 files that I need to QC regularly. I use a[ transpose - Join Name/Value - Join Name] pattern to identify discrepancies between the latest version of each table and the previous version. But sometimes it's hard to track down the root cause of the discrepancy. These tables have many columns. Using your macro, I can zero in on the rows the original tables that are different. Thank you!

Your macro works for me, but I wonder if now you would stick with this approach or suggest another approach?

Louise_Angerame

Coming in to say that this macro saved me about 30 minutes this morning. Thank you so much for sharing it!! Like another reply said, it may be old but it's solid. Thanks again!!

Cheers,

Steph

BS_THE_ANALYST

@JeffF awesome macro.

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335