Compare two fields for unique match

[Talbanese]

Hello,

Need help on comparing two fields and determining if its a match, if match is true than (in a new field) replace with true, else false.

Tried the If statements but its only replacing with the INDV ID if it matchs, else putting the FAM ID.

Please see example below.

Fam ID             INDV ID

123                   123                       Y 

124                   934                       N

125                   564                       N 

Thanks for the help

[s_pichaipillai]

try this

IIF([Fam ID]=[INDV ID],'Y','N')

[JohnJPS · Accepted solution]

The "Formula" tool will create a new field; name it whatever you prefer; and make it type boolean or string; then in the "Expression" area, test equality and set the value of the variable however you prefer.

[JordanB · Accepted solution]

Hi,

Please find an example workflow attached. 

Let me know if this is what your were looking for.

Best,

Jordan Barker

Client Service Representative 

[Talbanese]

Thanks

but i keep running into the error below

invalid tyoe in operator ==

[JohnJPS]

Is one side of the equality string and the other side integer, or any type mismatch of that nature? You could correct that by placing a "Select" tool before the "Formula" tool, and selecting the columns into matching data types.

[Talbanese]

Thanks,

Sorry just noticed it was a type mismatch. its not quite matching now cause INDV ID has trailing zeros. anyone know a good way to remove them? trim doesn't seem to work on removing them.

[JohnJPS · Accepted solution]

If you leave it as a string, then you can drop the zero characters easily; then convert to integer prior to compare; please see attached and modify as needed.

[Talbanese]

Perfect, thank you sir.