Numbers and strings

Example files with this article:
  • Numbers and strings
  • 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:
      1. 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:
      1. A real value
      2. A format string
      3. Decimal separator
      4. Thousands separator

    • STR
      - Arguments:
      1. A real value
      2. The string length
      3. 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:
      1. 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:
      1. A string
      2. Decimal separator
      3. Thousands separator

    • NUMBR
      - Arguments:
      1. 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!




    Homepage

    Section contents

    About Wim

    Wim Gielis is a Business Intelligence consultant and Excel expert

    Other links