Simple contract number lookup

[bhabura13]

Hello,

I'm trying to identify when a contract status has changed but for some reason am unable to get the new contract info for Contract #2 to return. I have two input files:

1. New Contract Info (Left)

Contract Number	Field 1	Field 2	Field 3	Contract Status	Field 4
1	blah	blah	blah	Active	etc
2	blah	blah	blah	Active	etc
3	blah	blah	blah	Terminated	etc

2. Old Contract Info (Right)

ID Number	Field 1	Field 2	Field 3	Contract Status	Field 4
1	blah	blah	blah	Active	etc
2	blah	blah	blah	Hold	etc
3	blah	blah	blah	Terminated	etc

I currently have a join and am returning the left unjoined values but contract #2 is not appearing. It will only appear when I return the Old Contract info... Contract Number and ID Number are the same

[lcrosby]

Hi,

Check out the workflow attached, I think it's doing what you need?

Your workflow only has the output on the left side, to make sure you will always capture all new orders (even if the old one is deleted/missing) you can union after the join tool on both the left and join outputs.

Having the workflow just output from the left side is basically saying you want to exclude any orders that match between left and right tables (as they all match in your example tables). So you need both left and right outputs unioned together to cover your bases.

[bhabura13]

Thanks lcrosby... this is getting me closer, but still not all the way:

I want to look to see if the contract number in the new file is in the old file

If it is, I want to check and see if the contract status has changed in the new file.

If the contract number matches AND the contract status has changed, then I want to return the entire line from the new file

[T_Willins · Accepted solution]

Hi @bhabura13 

I think this is what you are looking for.

[bhabura13]

Thank you! This is perfect