topic subfield extract requirement in QlikView

subfield extract requirement

sangland2 — Wed, 25 Nov 2020 16:16:04 GMT

Hi, Hoping someone has already done this. I have a product file that contains product description which includes text for KW,

eg "SYNERGY 110KW/180A SOFTSTARTER". I want to create a field during load to extract all values as 'KW", eg "110". The KW rating could be anywhere in the description string. Some combination of index and other text field methods locating "KW" then finding the preceeding space, then getting the text between this and KW. Thanks

Re: subfield extract requirement

ali_hijazi — Fri, 14 Sep 2018 07:48:34 GMT

the following should help:

load textbetween(your_field, ' ', 'KW') as KW

Re: subfield extract requirement

Arthur_Fong — Fri, 14 Sep 2018 07:59:50 GMT

Hi Simon,

Refer to attached using subfield for your requirement.

Thanks and regards,

Arthur Fong

Re: subfield extract requirement

sangland2 — Fri, 14 Sep 2018 13:18:45 GMT

Thanks, what if the description was "Some other SYNERGY 110KW/180A SOFTSTARTER",

Re: subfield extract requirement

sangland2 — Fri, 14 Sep 2018 13:33:06 GMT

Thank you both this was very helpful. This combined method works:

If there are multiple spaces before KW, eg

"Some other SYNERGY 110KW/180A SOFTSTARTER"

Load

subfield(Textbetween(ProductDesc,' ','KW'),' ',-1) as KW

- returns just 110

Re: subfield extract requirement

ali_hijazi — Fri, 14 Sep 2018 14:41:22 GMT

you can benefit from other string functions like:
load mid(ProductDesc,index(ProductDesc,' ',-1) + 1,len(ProductDes) - index(productDes,'KW')-1)