Find and replace based on a condition

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.

Dynamic Processing

Accepted answers

lmorrell

Hi @Jasdev

Updated workflow is attached

Find and replace based on a condition v2.png

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!

Find and replace based on a condition v2.yxmd

All comments

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

lmorrell

Hi @Jasdev

Workflow is attached

Find and replace based on a condition.png

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!

Find and replace based on a condition.yxmd

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.

postalcode_mappingworflow.PNG

Quick Links

Unanswered
Community Events
Groups

This months top contributors

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262

JORGE4900 235