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 |
Résolu ! Accéder à la solution.
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]
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!
You can connect False anchor to down stream tool or
add not to expression i.e not ( expression )
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?