Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

sifatnabil
Contributor III

Complex substring selections

Hi, I am trying to select particular substrings from a long string, but can't seem to formulate a way of doing this. This is the string, and colour-coded substrings I'd like to select:

"Received Order Insert message from gbarnfieldfsn. Instrument: 1M KRW Fix Midpoint, Mkt: 7. Order: Bid 10 @ 1.37."

I want to make new fields and input these substrings in them like below:

Instrument, B/O, Amount, Price

1M KRW, Bid, 10, 1.37

I can think of different operators like trim, index, right, left, len, etc. but finding it very difficult to select these without wildmatches. Any help greatly appreciated!

1 Solution

Accepted Solutions

Re: Complex substring selections

load

     MyString,Rest,

     Instrument,

     subfield(Rest,' ',2) as [B/O],

     subfield(Rest,' ',3) as Amount,

     subfield(Rest,' ',5) as Price;

load

     MyString,

     textbetween(MyString, 'Instrument: ','Fix') as Instrument,

     subfield(MyString,':',-1) as Rest;

load

     left(MyString, len(MyString)-1 ) as MyString

from ....


talk is cheap, supply exceeds demand
4 Replies

Re: Complex substring selections

Something like this:

load

     Instrument,

     subfield(Rest,' ',1) as [B/O],

     subfield(Rest,' ',2) as Amount,

     subfield(Rest,' ',3) as Price;

load

     textbetween(MyString, 'Instrument: ','Fix') as Instrument,

     subfield(MyString,':',-1) as Rest

from ....


talk is cheap, supply exceeds demand
sifatnabil
Contributor III

Re: Complex substring selections

Hi Gysbert,

Thanks a lot for this. However I'm getting it slightly misplaced like below:


I'm getting Amount under Price and B/O under Amount:

InstrumentB/OAmountPrice
1M MYR Offer50
1M CNY Offer50
1M MYR Offer3

Re: Complex substring selections

load

     MyString,Rest,

     Instrument,

     subfield(Rest,' ',2) as [B/O],

     subfield(Rest,' ',3) as Amount,

     subfield(Rest,' ',5) as Price;

load

     MyString,

     textbetween(MyString, 'Instrument: ','Fix') as Instrument,

     subfield(MyString,':',-1) as Rest;

load

     left(MyString, len(MyString)-1 ) as MyString

from ....


talk is cheap, supply exceeds demand
sifatnabil
Contributor III

Re: Complex substring selections

Perfect, thanks!

Community Browser