My data are structured such that I have one column with origin data, and one column with updated location data, and I am trying to make a column of "current" location. Updated location is only populated when relevant and is often blank.
I am trying to use a multi-field formula tool with the following logic:
IF (!IsEmpty([New Location])) THEN Replace([Origin], "(.*.*)",[New Location]) ELSE [New Location] ENDIF
Unfortunately the if statement doesn't seem to be effective, and the entire origin column is blanking out and being populated with the "New Location," so now Origin is just a copy of New Location. Similarly, if I replace the New Location in the ELSE statement with Origin, the New Location column then just is a copy of the Origin column.
What I am hoping to get in the Origin column is this:
canada
japan
argentina
mexico
Any assistance is much appreciated.
