I’m trying to use the replace function in a calculated field. The old_chars include individual phrases (ie ‘criminal - action’ and ‘criminal - trial’), as well as grouped phrases (ie ‘criminal - action,criminal - trial’).
The new_chars (‘criminal - success’) works fine for the individual phrases, but is replicated for the grouped phrases (ie the result is ‘criminal - success,criminal - success’).
How do I stop the repetition so that the calculated field returns a single response for the grouped phrases?
Hello @hopeah, would you be able to provide a screenshot of the calculated field you created? We may have to try some kind of ifelse statement to check if there are grouped phrased to return only 1. I think if I can see the calculated field you created, I may be able to figure out a way to remove the 2nd phrase from the field.
Thanks Dylan! Below is the calculated field and the current output. Some of the grouped phrase outputs are fine (see the fifth & sixth lines), but others are not (like the third & fourth).
Some of the grouped phrases (such as ‘Criminal - Arrest,Criminal - Charges,Criminal - Conviction,Criminal - Restitution’) show the correct output (Criminal - Success), but others do not (like ‘Criminal - Arrest,Criminal - Charges,Criminal - Conviction’), but I can’t identify any differences.
Hello @hopeah, I am not sure there is a good way to replace only the first string and not duplicate it with the way you have set up this function. You could maybe do something where you utilize substring and replace to eliminate any doubles in a single field value, and then run this function to replace the values.
Otherwise, I think at this point it would be easiest to make the update in your custom SQL when you query the data or even updating it in your data source. That would be my best suggestion.
