Join on All Matching Fields
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
- Labels:
- Join
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator