Join Tool - joining by all 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
I'm joining two files with VERY many columns. I want the join to match the data by all fields. Is there a quicker way to set up the tool to join by all fields besides selecting them one at a time in the configuration box? Seems like there would be an option to "join by all fields" but I'm not seeing it. Thanks!
Solved! Go to Solution.
- Labels:
- Join
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hey @Rob48, there's not a native 'join by all' option but - depending on the requirement - you could perhaps Transpose your data so you just have 2 columns [Name] and [Value] for each join input. Then conduct the join on [Name]=[Name] and [Value]=[Value] and Cross-Tab afterwards. Probably worth throwing a RecordID on there for grouping as well. Can mock something up if you provide some mock data!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Misread request
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
ok DataNath I'm taking you up on your offer. I always seem to struggle with Transpose and Crosstab! How about a mock up for these two sets of data. If I join by all fields I should get a match on users 4, 5, and 6. Thanks!
User Name | User Color | User Number | User City | User Age |
User 1 | Green | 1000 | Philadelphia | 31 |
User 2 | Green | 1000 | Detroit | 32 |
User 3 | Blue | 2000 | Chicago | 33 |
User 4 | Yellow | 3000 | Cleveland | 34 |
User 5 | Orange | 4000 | Louisville | 35 |
User 6 | Red | 5000 | Boise | 36 |
User Name | User Color | User Number | User City | User Age |
User 4 | Yellow | 3000 | Cleveland | 34 |
User 5 | Orange | 4000 | Louisville | 35 |
User 6 | Red | 5000 | Boise | 36 |
User 7 | Green | 6000 | Memphis | 37 |
User 8 | Blue | 7000 | Orlando | 38 |
User 9 | Indigo | 8000 | Los Vegas | 39 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks for that @Rob48! Have mocked something up here - did need to add a couple of extra steps to check that for each RecordID you have N fields where N = the number of fields you start with (in this example it's 5). This is because the join on Name and Value will create some cross record joins where one person may have the same age as another for example, but all of their fields won't match and so we exclude them. The format etc may need a little tidy up but hopefully this will help get you going. Note, because of what I have mentioned above, you'll need to update the Filter condition. It's currently set to Count = 5, but if you have 30 fields in your data for example, you'll need to change this to Count = 30.
![](/skins/images/A29875142F332EEF75F19ED75711F41B/responsive_peak/images/icon_anonymous_message.png)