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 IDColorStateSizeAnimal/MammalGeographyRPSCar/truck 47393GreenFLSPikaIslandRockCar 47393GreenFLSBobcatMountainPaperTruck 47393GreenCOMDolphinIslandScissorsCar 47393PurpleCOMDolphinPrairieRockTruck 47393PurpleCOMBobcatIslandPaperTruck 47393PurpleFLLPikaIslandScissorsCar 47393PurpleFLLMarmotMountainRockCar 47393RedIDMMarmotMountainPaperCar 47393RedIDMBobcatMountainScissorsTruck 47393RedCOSDolphinIslandRockTruck 47393RedFLLDolphinPrairiePaperCar 47393RedFLLDolphinPrairieScissorsCar File 2 IDColorStateSizeAnimal/MammalGeographyRPSCar/truck 47393GreenFLSPikaPrairieRockTruck 47393GreenFLSPikaMountainRockCar 47393GreenCOMDolphinIslandScissorsCar 47393PurpleCOMDolphinPrairieScissorsTruck 47393PurpleCOMDolphinIslandPaperTruck 47393PurpleFLLPikaIslandScissorsCar 47393PurpleFLLMarmotMountainRockCar 47393RedIDMMarmotMountainScissorsCar 47393RedIDMMarmotMountainScissorsCar 47393RedFLLDolphinPrairiePaperCar 47393RedFLLDolphinPrairieScissorsTruck 47393RedCOSPikaPrairieScissorsCar Desired Output IDColorStateSizeAnimal/Mammal 1Animal/Mammal 2File 1 GeographyFile 2 GeographyRPS 1RPS2File 1 Car/TruckFile 1 Car/Truck47393GreenFLS IslandPrairie CarTruck47393RedCOSDolphinPikaIslandPrairieRockScisscorsTruckCar

Compare two files (not based on Record ID)

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

Datasets

Reporting

Data Investigation

Accepted answers

rafalolbert

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?

workflow.yxmd

All comments

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!

rafalolbert

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?

workflow.yxmd

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.

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335