
Re: Monarch  converting string to number then IF range formula
Grant Perkins Aug 26, 2016 5:21 AM (in response to donh _)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,"NonProduction",
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,"NonProduction","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,"NonProduction","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