Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

parse a numerical value from a string text

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
1 Solution

Accepted Solutions
Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

View solution in original post

5 Replies
Peter_Cammaert
Partner - Champion III
Partner - Champion III

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
Not applicable
Author

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
Partner - Champion III
Partner - Champion III

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
Not applicable
Author

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 2021ID2001
month is november 70 % work done accounting age group < 8 Finance age Group > 10 fiscal year 2021ID2002
month is november 70 % work done accounting age group = 3  Finance age Group = 5 fiscal year 2021ID2003
Peter_Cammaert
Partner - Champion III
Partner - Champion III

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