Find replace with append returning null column

Question

Hello,

I am trying to use find and replace tool with append field settings to do a vlookup between 2 columns of different files and create a 3rd column to populate corresponding values.

Like: Column 1 of 1 file contains 123456, 234543 etc.

Column 10 of 2nd file contains again similar numbers, some common, some different.

Column 15 of 2nd file contains Names corresponding to that column 10.

So, Column 1 and column 10 of 2 files need to match and corresponding name must be appended in 3rd column.

Was working fine for other columns, but giving all null values in above use case even when matching data is there and names are there.

jrgo · Answer

It sounds like the columns you're setting your JOIN on aren't matching. it could be that your data from either source may have a space at the start or end of the value that you may not be noticing. Drop a data cleansing tool on both data sources before the JOIN tool with the option "Leading and Trailing Whitespace" checked under the "Remove Unwanted Characters" section.

As I previously mentioned, sharing an example workflow with dummy files that will reproduce the problem will allow us to better troubleshoot the problem.

ShantanuDagar · Answer

I tried using join tool as well by doing a vlookup on both.

But the column isn't joining. 0 records out of J. All records in file 1 are present in file 2.

Data types are same.

jrgo · Answer

The data that you're connecting into the R input, have you verified that the "Find Value:" field (column 10 I assume) is unique? Remember that this tool will only return the value for the first matched record so if a column 10 value is not unique, you may want to see what's the value in column 15 for the first one found.

If that doesn't seem to be the case, can you share a desensitized workflows + data file that we can troubleshoot? Screenshots go a long way as well. 🙂