Search based on one field and replace the values in another field using FindReplace Tool

[Rahulkanth]

Team, I have fields as follows:

Input File 1: 

Type	Description
HA	Hello

Input File 2:

Type Code	Doc Type
HA 01234	Hello

I would like to use the "FindReplcae" tool and use "HA" from Input file 2 as the Lookup value and find that in Input File 1 and get the Description value as "Hello" in the "Doc Type" field.

Please help, thanks in advance.

[OllieClarke]

Hi @Rahulkanth 

Here is how you'll want to set up the tool

Hope that helps,

Ollie

[Rahulkanth]

@OllieClarke  I already have a column named "Doc Type" where value needs to be replaced into that column.

Also, I have lot many columns with a similar situation. appreciate your help in advance, thank you.

[cjaneczko]

Can you provide more sample data on what your data looks like and how you want it updated? I came up with the same solution as Ollie based on your inquiry. His solution seems to do what was asked.

[Rahulkanth]

Hi @cjaneczko, I have the same solution suggested by @OllieClarke  to replace/append the field from the other Input file, but my question was to replace/populate the found values in the specified field which is already existing. 

[cjaneczko]

What is in the DocType field now? Is it null? We need more info to help. Does adding a formula tool after the Find Replace work?

if IsNull([Doc Type]) then [Description] else [Doc Type] endif

[OllieClarke]

Hi @Rahulkanth 
The Find and Replace tool can only replace a value in the same value as it looks. But you can append multiple fields.
What I did was append on the description field, and then drop the original field and rename the description field to be Doc Type in a select tool, so the output looks like you want:

If you have multiple columns that you need to update, then you'll need a different approach. Probably involving transposing your data...

[Rahulkanth]

Hi @cjaneczko, following is the real-time sample data for your reference:

Doc Type	Doc. No.
Accrual	JE 320000849
Actual	PS 320000850
Actual	IN 320000851
Actual	PC 320000852
Actual	KR 320000853
Actual	PS 320000854

The data in the "Doc Type" field above is the output that is needed, however, till the output is populated, the column will be having NULL values.

Doc Type	Desc	Description
PU	AP Invoice	Actual
PC	AP Credit Memo	Actual
JE	Journal Entry	Accrual
PS	Payment	Actual

Now based on the First table's "Doc No" first 2 chars, it needs to find/look into the 2nd table and then populate the Description into the 1st table's "Doc Type" column, however, if the data is not found, it must not populate anything. That is the requirement that I would like to execute with FindReplace Tool, if not with any other tool. Please help me with the workflow if possible, thank you.

[cjaneczko · Accepted solution]

[OllieClarke · Accepted solution]

@Rahulkanth 
I don't think your sample data is correct. IN and KR appear in the Doc. No., but not in the Doc Type column.

Having said that, the following workflow I believe should work for you.

Instead of the Find and Replace tool, we can just parse out the first 2 characters of Doc.No. using

LEFT([Doc. No.],2)

We can then join based on this and the Doc Type. We can also drop the original Doc Type and rename the Description Field to be Doc Type

Because of the IN and KR numbers, we need to union the Left and Join outputs (dropping the field we created from the Left output), to get our final table

Hope that helps,

Ollie