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!
