get particular word from line

[vengadeshpalani]

Hi all

I have a field like temp, I need to create field Min and Max by using temp field

temp
21 to 29 days old
210 to 29 days old
2001 to 2900 days old
21 to 229 days old

Ans

Min
21
210
2001
21

Max
29
29
2900
229

[surendraj]

try with sub field function..

SubField(temp,' ',1) as min,

SubField(temp,' ',3) as max

[Kushal_Chawda]

LOAD *,

         subfield(temp,'to',1) as Min,

         SubField(trim(SubField(temp,'to',2)),' ',1) as Max

.....

Edit: Updated Max value expression

[tresesco · Accepted solution]

Are you sure that your expected output is right? I guess from '210 to 29 days old', 210 should come under Max and 29 under Min. If so, you might try like:

RangeMax(SubField(temp,' ',1) , SubField(temp,' ',3) ) as Max,

RangeMin(SubField(temp,' ',1) , SubField(temp,' ',3) ) as Min