Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
qlikdash
Contributor II

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

Re: parse a numerical value from a string text

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

5 Replies

Re: parse a numerical value from a string text

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

qlikdash
Contributor II

Re: parse a numerical value from a string text

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

Re: parse a numerical value from a string text

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

qlikdash
Contributor II

Re: parse a numerical value from a string text

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

Re: parse a numerical value from a string text

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

Community Browser