Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I want to search for the position of second occurrence of a specific character ' ' within a string. Is there any string function for this that can do this?
Arif
how about the index() string function? See QlikView Help for more details.
substringcount(string, character)
this function calculates the number off occurences of character (or substring) in the string
Eduardo
Ok that worked. Many Thanks. Is there any function similar to index that can accept multiple patterns. For example, I want to search a text for 'Tier', 'Phrase', '-' etc and remove any text after these substring and return the result. For example, I have the following text/string
Alcohol Tier1
Anxiety Phrase
Depression-xyz
I would like to do something like left(observation, index(observation,'Tier','Phrase','-')) but I suppose index does not accept multiple patterns. Can I use some wildcard to provide multiple patterns?
Arif
like this?
source:
load * inline [
field
Alcohol Tier1
Anxiety Phrase
Depression-xyz
];
table:
load
field,
if(wildmatch(field, '*Tier1*', '*Phrase*', '*xyz*'),
left(field, index(field, pick(wildmatch(field, '*Tier1*', '*Phrase*', '*xyz*'), 'Tier1', 'Phrase', 'xyz'))-1)
) as newfield
resident source;
Or maybe like this:
MAP:
MAPPING LOAD F1, F1 & '|' INLINE [
F1
Tier
Phrase
-
];
source:
load * inline [
field
AlcoThol Tier1
Anxiety Phrase
Depression-xyz
];
table:
load
field,
subfield(MapSubString('MAP',field),'|',1) as newfield
resident source;
swuehl wrote:
Or maybe like this:
MAP:
MAPPING LOAD F1, F1 & '|' INLINE [
F1
Tier
Phrase
-
];
source:
load * inline [
field
AlcoThol Tier1
Anxiety Phrase
Depression-xyz
];
table:
load
field,
subfield(MapSubString('MAP',field),'|',1) as newfield
resident source;
MAP:
MAPPING LOAD F1, '|' & F1 INLINE [
F1
Tier
Phrase
-
];
source:
load * inline [
field
AlcoThol Tier1
Anxiety Phrase
Depression-xyz
];
table:
load
field,
subfield(MapSubString('MAP',field),'|',1) as newfield
resident source;
Right, this will remove all chars starting with the search string to the end, while I have read the OP's request to remove the chars after the search phrase. Depends what you need, but the change is easy.