1 Reply Latest reply: Aug 26, 2016 5:21 AM by Grant Perkins RSS

    Monarch - converting string to number then IF range formula

    donh _

      Help - - - using Monarch Pro 8.0

       

      I've successfully stripped out the numbers from a string (as characters) and trying to come up with a formula for ranges but not having any luck - - - monarch is not liking     .And.

       

      and have tried switching the General tab back and forth between character and numeric

       

      I am trying to tier but that does not work either

       

      if(val([ACCOUNT SPLIT])<=999,"",

      if(val([ACCOUNT SPLIT])>=1000,"Production",

      if(val([ACCOUNT SPLIT])>=10000,"Non-Production","PROB")))

       

      What I'm trying to get at is if the account # is less or equal to than 999 then blank

      between and including 1000-9999 then Production

      between and including 10000-99999 then Non-Production

       

      Any help would be appreciated

       

      Thanks

       

      Don

        • Re: Monarch - converting string to number then IF range formula
          Grant Perkins

          Don,

           

          What result are you seeing or do you get an error message?

           

          Do the part of the formula work for you if separated rather than nested? i.e. create one field for each IF() and check you get the expected results

           

          Using V12 (for this formula it should not make a difference if used with V8) I ran a quick test on a list of number  - so I have not checked your VAL() transformation but it should be OK.

           

          With the order of the IF() comparisons changed things work

           

          if([Acc number]<=999,"",

          if([Acc number]>=10000,"Non-Production",

          if([Acc number]>=1000,"Production","PROB")))

           

          This deals with the "ends" of the ranges first so that whatever has not been categorised falls into the middle range.

           

          The alternative would be to add some complexity to the formula by specify the match would need to be >= to 1000 and <=9999.

           

          A slightly different approach might be to leave the logic order the same and use the .IN. operator for the middle range of account numbers.

           

          if([Acc number]<=999,"",

          if(int(([Acc number]/1000)).In.(1,2,3,4,5,6,7,8,9),"Production",

          if([Acc number]>=10000,"Non-Production","PROB")))

           

          As .IN. requires a list of compare values and the idea of listing 1000 to 9999 did not appeal to me I reduced the size of the list by dividing the numeric account number by 1000 and specifying just the INTeger was required. There are probably a number of ways that one might do something similar to reduce the list required by .IN.

           

          For you particular example one could also work with the length of the data to compare to rather than the number.

           

          You have codes of either 1 to 3, 4 or 5 digits/characters.

           

          So something like

           

          if(LEN(trim(STR([Acc number])))<=3,"",

          if(LEN(trim(STR([Acc number])))=4,"Production",

          if(LEN(trim(STR([Acc number])))=5,"Non-Production","PROB")))

           

          would work.

           

          Note that the STR() function is required as I am starting with a numeric field and LEN works for characters. Trim may or may not be required but is always a safe option.

           

           

          The resulting field needs to be Character type.

           

          I hope these ideas help.

           

          Grant