Isolate Text In String (Various Formats)

[caseyjohnson]

I'm trying to isolate some characters from a field (in the load editor) that can be stated various ways. There could be text in front, back, or both. Examples below

FIELD_X:

ABC ?,

ABC ?(

ABC ??,

ABC ??(

... ABC ?,

ABC ??? ( ........

TRYING TO ISOLATE (?)

My attempt:

if (OTHER_FIELD = 'x', TextBetween(FIELD_X, 'ABC', ','),  

if (OTHER_FIELD = 'x', TextBetween(FIELD-X, 'ABC', '(') )) as desired_new_field,

This sort of works, but it only works for first statement and not for the second?? How can I write this for the mutliple formats?

[rwunderlich]

Try

SubField(Subfield(SubField(FIELDX, 'ABC ',2),',',1),'(',1) as Newfield

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

http://www.easyqlik.com

[petter]

A way to do it if you need to have a flexible wildcard pattern-list that should be matched upon:

LOAD
.....
Pick(
  // First determine the match pattern #
  WildMatch(
    FIELD_X 
    ,'ABC ?,*'  
    ,'ABC ?(*'  
    ,'ABC ??,*'  
    ,'ABC ??(*'  
    ,'* ABC ?*'      // ! Must be in synch with line #20
    ,'ABC ??? (*'
  )+1              // Add one so we have something to pick as #1 if no match
  ,'' // NO MATCH as an empty string or replace with Null() if that's better
  // Pick the right character(s) based on the pattern # determine above
  ,Mid(FIELD_X,5,1)
  ,Mid(FIELD_X,5,1)
  ,Mid(FIELD_X,5,2)
  ,Mid(FIELD_X,5,2)
  ,Mid(FIELD_X,Index(FIELD_X,' ABC ')+5,1) // ! must be in sync with line #11
  ,Mid(FIELD_X,5,3)
) AS desired_new_field,
....

If I am wrong in assuming that you want arbitrary text to come after all the patterns then just remove the star at the end of the patterns from line 5 to 10.

I assumed that you needed to pick a single character exactly, or two characters exactly or three characters exactly - not just any number of characters so the ? mark in the patterns are exactly that - match for one and only one character for each ?.

And if you need to have just a match on alphanumeric characters you should wrap the Pick function in a KeepChar function:

KeepChar( Pick(....... 
....
....) , 'ABCDEFGHIJKLMNOPQRSTUVWXYZ01234567890') AS desired_new_field,