Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I want to extract delimiter. I understand that Subfield function can be used if we want to get content before or after delimiter. But in my column named Location values are
Boxborough, MA |
Brazil, Sao Paulo |
Bridgewater, NJ |
California - San Diego |
Canada, Markham |
Using below code in the script I am able to extract the values in green, but not the value in red
if("NH Country" = 'USA',TRIM(Subfield("LOCATION",',',1)),(if("NH Country" <> 'USA',TRIM(Subfield("LOCATION",',',2))))) AS "PLCity",
Can someone help?
Thanks
You could try to replace all - with a , before performing your trim logic.
Thanks but when there is a - I need to pick city after the delimiter and when there is , I need to pick city before , so the replace logic wont work correctly
The delimiter in both subfields is the same and therefore the logic couldn't work. Personally I would follow the suggestion from Vegar to unify the different delimiter and then transferring the condition into the third parameter of the subfield, means something like this:
trim(subfield(replace(LOCATION, '-', ','), ',',
if([NH Country] <> 'USA' or substringcount(LOCATION, '-'), 2, 1)))
Depending on the variety of your delimiter and/or the order in which the values are placed you might need to add further conditions and/or to adjust them here. If the variety of the above mentioned is quite big but there aren't really many values it might be an alternatively to create a (manually) mapping table for it and replace the values with it.
- Marcus
I may be way off-base here, but what about two different loads of the data, one where you grab all the records with the ,'s and another where you grab the -'s, that way your subsequent logic can be properly applied to each and you can then join things properly from those two Resident tables you end up having? Apologies again if I am way off here, but from what I could gather, this seemed like a potential way to go with things given the use case.
Regards,
Brett