Search based on one field and replace the values in another field using FindReplace Tool
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Labels:
- Input
- Join
- Preparation
- Tips and Tricks
- Workflow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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
![](/skins/images/0052A40480681DBBC707042CBFDD66A8/responsive_peak/images/icon_anonymous_message.png)