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.
Solved! Go to Solution.
@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.
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.
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.
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
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...
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.
@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