Check values in a Referenced Table

[tjamal1]

Hello,

We have a column which contains 1000 records with a column name 'country'

I need to check whether each of this column value value match our reference country table which contains only two values

United States 

Canada

Note: Matching should not case sensitive 

[Jonathan-Sherman]

Hi @tjamal1,

So the first step would be to convert the country fields in both tables to upper or lower case just in a formula tool and then join both tables together on the country field using a join tool. Those records which come out of the "J" output anchor match the reference table, those which fall out of the "L" or "R" output anchors do not appear. You can then remove the added column using a select tool if you need.

If this solves your issue please mark the answer as correct, if not let me know! If you attach some sample data i'll put up an example for you.

Regards,

Jonathan

[tjamal1]

Thanks for reply.

I want to keep both un match and unmatch records together and create another column which says matched record and unmatched record

[Jonathan-Sherman · Accepted solution]

So after the join you would want a formula tool connecting to output anchors to create a new "Matched / Unmatched" column and union both results together

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow using some mocked up data for you to download if needed.

Regards,

Jonathan