topic Re: Complex substring selections in QlikView

Complex substring selections

sifatnabil — Tue, 17 Jun 2014 12:03:09 GMT

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!

Re: Complex substring selections

Gysbert_Wassenaar — Tue, 17 Jun 2014 12:08:25 GMT

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 ....

Re: Complex substring selections

sifatnabil — Tue, 17 Jun 2014 12:28:54 GMT

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:

Instrument	B/O	Amount	Price
1M MYR		Offer	50
1M CNY		Offer	50
1M MYR		Offer	3

Re: Complex substring selections

Gysbert_Wassenaar — Tue, 17 Jun 2014 12:41:39 GMT

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 ....

Re: Complex substring selections

sifatnabil — Tue, 17 Jun 2014 12:54:06 GMT

Perfect, thanks!