parse a numerical value from a string text

[Anonymous]

Hi Qlikkers,

I have a Field with String from which I need to parse a numerical value.

Only for month of November, I need the value parsed in a calcualted field in my script.

so in below, I need to parse 3, 300, 32,4,3,300,3,3. how to do that.

I am using :if(wildmatch(Text Field, '*november*')>0, mid([Text Field] ,index([Text Field],'age group < ')+12,3,'Value')) as single_value

but its not working.

[Text Field] 

month is november 70 % work done accounting age group < 3 fiscal year 2015

month is november 70 % work done accounting age group < 300 fiscal year 2016

month is november 70 % work done accounting age group < 32

month is november 70 % work done accounting age group < 4

month is november 70 % work done accounting age group < 3 fiscal year 2018

month is november 70 % work done accounting age group < 300 fiscal year 2019

month is november 70 % work done accounting age group < 3 fiscal year 2020

month is november 70 % work done accounting age group < 3 fiscal year 2021

month is december 70 % work done accounting age group < 41

month is december 70 % work done accounting age group < 3 fiscal year 2018

[Peter_Cammaert · Accepted solution]

How about this?

... if(wildmatch([Text Field], '*november*'),

       num#(TextBetween([Text Field], '< ', ' '))) as single_value...

Note that the first delimiter string contains a smaller-than character and a space. The second one contains just a single space.

Best,

Peter

[Anonymous]

month is november 70 % work done accounting age group > 300 fiscal year 2019

month is november 70 % work done accounting age group > 3 fiscal year 2020

month is november 70 % work done accounting age group > 3 fiscal year 2021

Peter, Thank you. I forgot to mention , some of them also have > , as shown above. how to handle this

[Peter_Cammaert]

Okay, I hope your strings aren't too flexible in this.

Change your expression into:

... if(wildmatch([Text Field], '*november*'),

       num#(subfield([Text Field], ' ', 12))) as single_value...

Only on condition that your sentences do not contain any double or leading spaces.

Best,

Peter

[Anonymous]

I have the following requirements on top of existing above reqs:

  sometimes the records contain 2 diff text which I will need to use as 2 diff fields later; if it has both

'accounting age group' and 'Finance age Group' in the same row, i need the corresponding numerical values to b parsed out in two diff fields. so  for ea IDs, I need 2 new fields, accounting age and finance age.

one more addition is '=' sign, which is ok, as long as ID2003 has finance age as 5 value and acc age as 3 value, it is valid.

how can I achieve this

month is november 70 % work done accounting age group < 3 Finance age Group < 5 fiscal year 2021	ID2001
month is november 70 % work done accounting age group < 8 Finance age Group > 10 fiscal year 2021	ID2002
month is november 70 % work done accounting age group = 3  Finance age Group = 5 fiscal year 2021	ID2003

[Peter_Cammaert]

I would suggest to do the value checking in a later step. First you need to lift all required values from each string and put them in separate fields. You can do that using the same technique as explained earlier on. For example:

... if (wildmatch([Text Field], '*november*'),

        num#(subfield([Text Field], ' ', 12))) as AccountingAge,

    if (wildmatch([Text Field], '*november*Finance age Group*'),

        num#(subfield([Text Field], ' ', 13))) as ID,

    if (wildmatch([Text Field], '*november*Finance age Group*'),

        num#(subfield([Text Field], ' ', 18))) as FinanceAge ...

and so on. Make sure that your strings do not contain multiple successive spaces.

Peter