Find and replace based on a condition
- 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
Hi,
I have a scenario where I am struggling and is kind of really urgent for me. I will explain with sample example below first:
File 1:
State City Fname
GA Atlanta Jaszy
File 2:
State City Fname Lname
GA Atlanta Jasdev
GA Atlanta
GA Atlanta random Singh
Output Result should be only File 1:
State City Fname
GA Atlanta random
I am doing find and replace and it picks up the last occurrence where it puts a new column in File 1 and enters value as random. This certainly is being picked up as that's the last occurrence of combination State and City (which in my scenario I am using to do find). Is there a way where I can enter a particular if statement in the Find and replace tool where I can write, if you find a hit with combination of State and City, pick that one where Lname is empty and the Fname isnt empty. If not, keep the same value that was there in the Fname attribute already in File1.
Please help me as soon as possible.
Solved! Go to Solution.
- Labels:
- Dynamic Processing
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
What is your desired output? According to your example, Fname in file1 should go from Jaszy to Jasdev (because this is the row in file2 where Lname is empty but Fname is not.
Do I have this right?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I apologize for typo.
Output Result should be only File 1:
State City Fname
GA Atlanta Jasdev
I am doing find and replace and it picks up the last occurrence where it puts a new column in File 1 and enters value as random. This certainly is being picked up as that's the last occurrence of combination State and City (which in my scenario I am using to do find). Is there a way where I can enter a particular if statement in the Find and replace tool where I can write, if you find a hit with combination of State and City, pick that one where Lname is empty and the Fname isnt empty. If not, keep the same value that was there in the Fname attribute already in File1. If all rows in file 2 had some value for Lname, it would keep it as Jaszy.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Jasdev
Workflow is attached
From what I understood from your write-up, there is an issue with the Find & Replace Tool attaching the last record where State+City are the same - when the desired output should be where the State+City are the same AND the Fname is not null AND the Lname is null. If this is the case, instead of applying a formula within the Find & Replace Tool itself, we can use an IF statement via a Filter Tool upstream of the Find & Replace to match only records where Fname is not null and Lname is null. Then a Formula Tool updates the original Fname column.
Although, while working through this - if the requirements to attach Fname were to change then you can also use a series of Join Tools to build in more flexibility to the logic behind IF statements. By adding a Record ID to File1, Joining on City+State, and then implementing the logic to take non-null Fnames and null Lnames from File2, and retaining all records that do not match - then the same result can be achieved.
Hope this helps!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hello @lmorrell
Thank you for your response!
In the 2 solutions you provided, second one isn't feasible as I have multiple find and replace in my workflow. You can check 6 of them are there out of which this logic needs to be in middle 2 one (red circles). If I use the concept of Record ID and join, it would create alot of other tedious issues for me for proceeding processes,
For the first solution, I was really looking forward for that but a condition that you have placed for file 2 before find and replace (!isnull([Fname]) AND isnull([Lname]) , I can have that as I can't suppress records from file 2, they are used somewhere too.
When i removed that condition and ran the workflow, it was picking the wrong value so back to square 1.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Jasdev
Updated workflow is attached
Wow! Seeing the workflow I can definitely see why you wouldn't want to make it more complex.
In this case, knowing that the Find & Replace tool will always append the last record where the Find & Replace condition is true - we can create an additional column ([Sort order]) in File 2 that assigns a value of 1 where Fname is not null and Lname is null, and a value of 0 where it is not. Sorting on this column will ensure that any time this condition is true, the Find & Replace will append this information and not a separate one.
However, regarding your second point and from what I gather seeing the screenshot, it could be possible to suppress records just for the two middle Find & Replace Tools, and union the True and False datastreams when using the records downstream.
Hope this helps!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I think this is going to resolve my problem. As my data set is huge, I am going to implement this in my workflow and do the testing and update you here.
But sir, thank you so much for your quick response.
