topic Re: How to extract a delimiter in QlikView

How to extract a delimiter

rdsuperline — Sat, 16 Nov 2024 02:24:03 GMT

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

Re: How to extract a delimiter

Vegar — Tue, 24 Sep 2019 21:18:46 GMT

You could try to replace all - with a , before performing your trim logic.

Re: How to extract a delimiter

rdsuperline — Tue, 24 Sep 2019 21:40:04 GMT

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

Re: How to extract a delimiter

marcus_sommer — Wed, 25 Sep 2019 12:00:32 GMT

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

Re: How to extract a delimiter

Brett_Bleess — Thu, 03 Oct 2019 20:35:49 GMT

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