Split out a word from a long text - one of several

[datanibbler]

Hi,

in the Excel_file I'm currently working on:

- There is a field named "comments" where the customer can enter whatever and as much as they want;

- In that field, there might or might not be any of the words "A-Service", "B-Service", C-Service", "DGR" or "Flash".

=> I can use the INDEX() function to look for the word "Service" and then use the MID() function to single out the full word.

<=> How about the others? How can I pass several words to the INDEX() function?

Thanks a lot!

Best regards,

DataNibbler

[simondachstr]

How about splitting every word into its own record first using subfield and then in a resident load work with the index function. Just a quick idea..

[marcus_sommer]

Hi DataNibbler,

you could use pick(wildmatch()), maybe so:

pick(wildmatch(upper(Comment), '*SERVICE*', '*DGR*', ....), 'A', 'B', ....)

- Marcus

[datanibbler]

Hi Marcus,

that looks good - for the first half of my requirement:

Using this, I could find out whether any of those words is contained in the text.

<=> The second half is more difficult here: If any of those words is present, I need to find out

        exactly where in the comment it is and then

        - If the word is 'DGR' or 'FLASH', I just need to repeat exactly this

        - If the word is 'SERVICE', then I need to subtract 2 from its position and
         => return 9 letters from that position ("A-SERVICE" or "B SERVICE" or whatever there is)

I think I have to go about this word-wise and use the output of INDEX() to find out if one of these words was found and then act accordingly. That will make for a rather long IF_construct. Not very elegant, but if it works ...

Still, if you have any better idea, I'm keen to hear it.

Best regards,

DataNibbler

[ali_hijazi]

use WildMatch as follows

if(WildMatch(Comments, '*word1*','*word2*',...) > 0, do something , do something else)

wildmatch makes non-case-sensitive search

[marcus_sommer]

Hi DataNibbler,

the "ugly" nested if-loop with index() will be the easiest way - maybe you could also use a customized function within the pick-expression - see here: http://community.qlik.com/blogs/qlikviewdesignblog/2014/10/03/teaching-the-load-script-new-tricks

- Marcus