For your specific example:
REPLACE([Text field], "JFK", "New York")
Hi Sam! The easiest way to do this is to use two nested REPLACE() functions, like this:
REPLACE(REPLACE([Dimension], "LGA", "New York"), "JFK", "New York")
I would create a calc like this:
IF FIND( [dimension field], "LGA") >0 then
REPLACE([dimension field], "LGA", "LaGuardia")
IF FIND( [dimension field], "JFK") >0 then
REPLACE([dimension field], "LGA", "New York")
IF (insert as many of these as you need)
END END ... END
then just use that calc in place of the actual dimension field.
Oh, and if you need to do TWO REPLACE() operations (one for the first half, one for the second half) then have two calcs so that the second uses the first calc as the input instead of [dimension field].
Or, just ETL this data before sending it into Tableau.
OK, I like David's suggestion better. I envision that there will be need for more than just LGA and JFK to be changed, but one nested string of REPLACE functions will get all the permutations in on pass rather than my suggestion of two calcs doing it in two passes.
True, it could be a very deep and gnarly series of nestings if there are 100 different airports in question, but still, it can be done.
Yeah, that would be pretty gnarly! I think that once you get above a few replacements, it'd be better to ETL the data (as you said) or maybe use a secondary data source for matching.
Incredibly helpful. Can't thank you enough.
Happy to help!