Find and replace based on a condition

[Jasdev]

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.

[PhilipMannering]

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?

[Jasdev]

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.

[lmorrell]

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!

[Jasdev]

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.

[lmorrell · Accepted solution]

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!

[Jasdev]

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.