I have a field that needs to be extract in to 2 fields.
I've used subfield function to extract 1 field into 2 fields 'country' and 'imex' then I used the trim function to remove unnessary spaces within the field imex. Now I want to add the replace function to change some fieldvalues. My script now looks:
if(trim(subfield(Fieldvalue, ',', 2)),
if(replace(Fieldvalue, 'EP', 'E'))))) as imex,
However this is incorrect. I don't know how to combine these 3 function into one formula. if you combine multiple function, how do you know which one you should use first. In my case, should I start with: the replace function then the trim and subfield? or first the trim(subfield then the replace function?
I'm not sure you need the if statements. As you have multiple scenarios where you are wishing to replace values, I would use the mapsubstring function and create a map with all the values you wish to replace.
The mapsubstring function then goes around the subfield and trim functions.
First should be SubField(), since you need to get the value from the greater part. Second each of the replaces, then the Trim(). You can do the Trim() then the Replace(), but always first the SubField().