Join Tool - joining by all fields

[Rob48]

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!

[DataNath]

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!

[ed_hayter]

Misread request

[Rob48]

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

[DataNath · Accepted solution]

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.