5 Replies Latest reply: Mar 3, 2016 8:28 PM by Peter Cammaert RSS

    parse a numerical value from a string text

    John Jackson

      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
        • Re: parse a numerical value from a string text
          Peter Cammaert

          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

            • Re: parse a numerical value from a string text
              John Jackson
              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
                  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

                  • Re: parse a numerical value from a string text
                    John Jackson

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