subfield extract requirement

[sangland2]

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

[ali_hijazi]

the following should help:

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

[Arthur_Fong]

Hi Simon,

Refer to attached using subfield for your requirement.

Thanks and regards,

Arthur Fong

[sangland2]

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

[sangland2 · Accepted solution]

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

[ali_hijazi]

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