Numbers and strings
 Jan. 16, 2014
Example files with this article:  
Introduction
In Turbo Integrator processes and business rules, you regularly convert a string to a number and a string to a number. This can be a pain in the backside. Just think about loading actuals coming from different ERP systems, following different decimal separators, thousand separators, and so on. Or exporting data for Hyperion (for example, 7 decimals). And countless other situations.
Below is a chart on the 2 times 3 functions that we can use to do the conversions. It has got nothing revolutionary, but I find it interesting to have an overview that I can consult on a regular basis. Right, until the moment I know this information by heart (as opposed to, me thinking that I know this information by heart).
Overview chart
From a number to text

NUMBERTOSTRING (TI only)
 Arguments: A real value
 Input: the decimal separator for the current user locale!
 Output: similar to the 'general' number format: no thousands separators and  for negative numbers 
NUMBERTOSTRINGEX (TI only)
 Arguments: A real value
 A format string
 Decimal separator
 Thousands separator

STR
 Arguments: A real value
 The string length
 The number of decimals
 Input: you must use . (period) as the decimal separator and , (comma) as the thousands separator
 Output: if necessary, the function inserts leading blank spaces to attain this length. Hence, use TRIM.
From text to a number

STRINGTONUMBER (TI only)
 Arguments: A string
 Input: the decimal separator for the current user locale!  When converting to a number is not successful, the process will abort. 
STRINGTONUMBEREX (TI only)
 Arguments: A string
 Decimal separator
 Thousands separator

NUMBR
 Arguments: A string
 Input: you must use . (period) as the decimal separator and , (comma) as the thousands separator
 Output: All characters other than '0' through '9', '+', '', ',', '.' and 'E' are ignored.
Examples
Right, we got that! Now here are 3 examples of the usage. In the first example, we move 1 in a typical Periods dimension, using business rules: adding 1 to a period (string) as in 01, 02, 03, …, 12 (Rules). Note: Adding a Text attribute to the dimension is by far the better option.
IF(NUMBR(!Rp_Period) < 9, '0', '')  TRIM(STR(NUMBR(!Rp_Period)+1, 2, 0))
In the second example, we will loop through a dimension with 24 forecast scenarios (spanning 2 years), Fct_01 until Fct_24:
f = 1;
WHILE( f <= 24 );
vFctScenario = 'Fct_'  NUMBERTOSTRINGEX( f, '00', '', '' );
# other code
f = f + 1;
END;
You see that there is no need to check whether f is smaller than 9 or not, because then we need to add leading 0. The NUMBERTOSTRINGEX function takes care of this. The last 2 parameters in NUMBERTOSTRINGEX are empty since we are looping from 1 to 24 over whole numbers smaller than 1000.
Lastly, we will be reworking negative values with parentheses to a minus sign (Turbo Integrator coding):
vAmount = '(123,45)'; vAmount = Trim(vAmount); IF(SCAN('(', vAmount) = 1 & SCAN(')', vAmount) = LONG(vAmount) ); vAmount = ''  TRIM(SUBST(vAmount, 2, LONG(vAmount)  2)); ENDIF; IF(SCAN('(', vAmount) > 0 % SCAN(')', vAmount) > 0 ); ITEMREJECT('Unknown data format for '  vAmount); ENDIF; vAmount_N = STRINGTONUMBER(vAmount); IF(vAmount_N <> 0); # ... ENDIF;
Right, that’s it for today!