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

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.

Tips and Tricks

Join

Interface Tools

Accepted answers

DataNath

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.

Get Area Code from template table.yxmd

All comments

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.

New Workflow3.yxmd

DataNath

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:

Get Area Code from template table.yxmd

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.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703