Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

How to extract a delimiter

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

Labels (2)
4 Replies
Highlighted
Partner
Partner

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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted
Contributor
Contributor

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

Highlighted
MVP & Luminary
MVP & Luminary

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

Highlighted
Digital Support
Digital Support

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.