Compare two files (not based on Record ID)

[Empower49]

Hello. I have two files with the structure as below and would like to compare the records with the same ID, color, state and size and determine differences in the remaining fields. I can't compare based on record ID, but want to compare based on the first four fields of the record. Does anyone have advice? Thank you! 

File 1
ID	Color	State	Size	Animal/Mammal	Geography	RPS	Car/truck
47393	Green	FL	S	Pika	Island	Rock	Car
47393	Green	FL	S	Bobcat	Mountain	Paper	Truck
47393	Green	CO	M	Dolphin	Island	Scissors	Car
47393	Purple	CO	M	Dolphin	Prairie	Rock	Truck
47393	Purple	CO	M	Bobcat	Island	Paper	Truck
47393	Purple	FL	L	Pika	Island	Scissors	Car
47393	Purple	FL	L	Marmot	Mountain	Rock	Car
47393	Red	ID	M	Marmot	Mountain	Paper	Car
47393	Red	ID	M	Bobcat	Mountain	Scissors	Truck
47393	Red	CO	S	Dolphin	Island	Rock	Truck
47393	Red	FL	L	Dolphin	Prairie	Paper	Car
47393	Red	FL	L	Dolphin	Prairie	Scissors	Car
File 2
ID	Color	State	Size	Animal/Mammal	Geography	RPS	Car/truck
47393	Green	FL	S	Pika	Prairie	Rock	Truck
47393	Green	FL	S	Pika	Mountain	Rock	Car
47393	Green	CO	M	Dolphin	Island	Scissors	Car
47393	Purple	CO	M	Dolphin	Prairie	Scissors	Truck
47393	Purple	CO	M	Dolphin	Island	Paper	Truck
47393	Purple	FL	L	Pika	Island	Scissors	Car
47393	Purple	FL	L	Marmot	Mountain	Rock	Car
47393	Red	ID	M	Marmot	Mountain	Scissors	Car
47393	Red	ID	M	Marmot	Mountain	Scissors	Car
47393	Red	FL	L	Dolphin	Prairie	Paper	Car
47393	Red	FL	L	Dolphin	Prairie	Scissors	Truck
47393	Red	CO	S	Pika	Prairie	Scissors	Car
Desired Output
ID	Color	State	Size	Animal/Mammal 1	Animal/Mammal 2	File 1 Geography	File 2 Geography	RPS 1	RPS2	File 1 Car/Truck	File 1 Car/Truck
47393	Green	FL	S			Island	Prairie			Car	Truck
47393	Red	CO	S	Dolphin	Pika	Island	Prairie	Rock	Scisscors	Truck	Car

[JReid]

You could join on those four fields and then use a filter tool with the custom code

[Animal/Mammal] != [Right_Animal/Mammal] OR
[Geography] != [Right_Geography] OR
[RPS] != [Right_RPS] OR
[Car/truck] != [Right_Car/truck]

[Empower49]

Thank you JReid! I think this is close... the "false" filter is returning the correct records, but the "true" filter is returning all records. I would like the output to only include the mismatches, not all of the data. Do you have any advice? Thank you!

[benakesh]

You can connect  False anchor to down stream tool    or   

add   not  to  expression  i.e not ( expression ) 

[rafalolbert · Accepted solution]

Hi @Empower49,

This should work:

- join on all 4 common fields

- retain the J output

- using Filter tool identify records where at least one remaining fields are mismatched

- for each remaining fields reset both sides to Null if they match and retain values if they don't

Thanks,

Rafal

#Excuse me, do you speak Alteryx?

[Empower49]

Thank you very much Rafalolbert! This is exactly what I need. I really appreciate you sharing your expertise - thanks for taking time to post a solution. I look forward to doing the same with the community in the future.