Lookup field in second data source and return value from second column

[datadabbler]

What is the best way to solve the following problem? I have data source one with many columns, one of which is "City" which is filled, another is "Area Code" which is empty. I have a second data source which is two columns "templateCity" and "templateAreaCode". I want to fill "Area Code" with the "templateAreaCode" matched on "City" to "templateCity". I can use Find and Replace tool, but that overwrites a columns data (I think?!), I need to fill "Area Code" with the corresponding city area code?  Thanks.

[CarliE]

Hi @datadabbler,

Is there a dummy dataset you can attach? What i think you will want to do is use a join tool rather than a find and replace.

This is what I was thinking

If this helped to solve your issue, please make sure to mark it as a solution!

[gautiergodard]

Hello @datadabbler 

Would you be able to use the join tool for this?

I've mocked up a quick example to get you started.

[DataNath · Accepted solution]

You can use Find & Replace for this, you just need to use the 'Append Field(s) to Record' option instead of the 'Replace Found Text With Value' selection:

However, @CarliE's solution here is definitely better as the Join tool has built in Select functionality so you can clean up field names and remove the unnecessary ones without adding a separate Select tool that you'd have to do with the F&R approach.

[datadabbler]

I used the F&R followed by the Select tool and that produced the desired results. The Join tool did work, but it changed/broke the output file column order. I am trying to figure out why, as it probably is the neater solution. Anyhow, thanks all.