Planning Analytics with Watson

 View Only
Expand all | Collapse all

TI Process, NumberToString is language dependent

  • 1.  TI Process, NumberToString is language dependent

    Posted Fri January 27, 2023 10:02 AM
    Edited by Asgeir Thorgeirsson Fri January 27, 2023 10:29 AM
    Hi all

    In a TA Process, the function NumberToString() seems to be language dependent. 
    This is a problem when for example someone in the US presses a button to export data to a file and someone in Europe presses a button to import the file. 
    Also if collages working side by side us the different browser language as default* 
    The decimal format of the data must be the same for both ends.  

    I also noticed that the functions NumberToStirng() and STR do behave differently.
    NumberToString() does not use the defined DataSourceASCIIDecimalSeparator but the STR function does. 

    Here is the question:
    Do you have a nice solution or setting for me to make the cast function NumberToString() independent of the browser language?


    I created the following example to test some cast methods available
    Using the SVALUE will work but In some cases, I need to invert the VALUE.
    If I invert the value like this, VALUE=-VALUE then the SVALUE is not inverted and therefore cannot be mapped directly to the export. 
    Some casting of the VALUE to text is then needed. 

    #Section Prolog

    #Section Data

    The result if I run this from a browser set to English
    Function   SVALUE     Result        Code
    SVALUE  115052.18   115052.18  SVALUE ...not inverted
    NTS         115052.18  -115052.18  ValueNTS=NumberToSTring(VALUE) ...decimal seperator is language dependent 
    NTSX      115052.18  -115052.18  ValueNTSX=NumberToStringEx(VALUE, #.#0, DataSourceASCII...  )
    STR         115052.18  -115052.18 ValueSTR=TRIM(STR(VALUE,12,2) )

    The result if I run this from a browser set to the local language, in my case Icelandic
    Function   SVALUE     Result        Code
    SVALUE  115052.18   115052.18  SVALUE  ...not inverted
    NTS         115052.18  -115052,18  ValueNTS=NumberToSTring(VALUE) ...here is a problem. Wrong decimal separator
    NTSX      115052.18  -115052.18  ValueNTSX=NumberToStringEx(VALUE, #.#0, DataSourceASCII...  )
    STR         115052.18  -115052.18 ValueSTR=TRIM(STR(VALUE,12,2) )


    * The topmost language in the browser settings defines the regional format of numbers and dates 
    • chrome://settings/languages
    • edge://settings/languages
    • etc...


    ------------------------------
    Asgeir Thorgeirsson
    ------------------------------


  • 2.  RE: TI Process, NumberToString is language dependent

    Posted Fri January 27, 2023 12:41 PM
    Hi @Asgeir Thorgeirsson

    I used 2 approaches to deal with cases like yours.

    1. Create a script to analyse the first couple rows of your file and try to guess the delimiters (the precision is limited to the cases you try\catch), then output a file with the estimated locale settings. You call the script in the Prolog, then based on the output file you set the correct datasource delimiters.
    PROs: this approach can be used to import files exported\created from other applications (i.e. Excel)
    CONs: The precision is limited to the cases you analyse. I always had some users who customise their regional settings and you can always get some head pain

    2. You can use NumberToStringEx and StringToNumberEx​.
    PROs: you can control the delimiters when exporting and importing
    CONs: works greats if the data was exported by another TI process and you control the export numeric format. You cannot rely on this approach when the data is coming from other sources.

    ------------------------------
    Vlad Didenko
    Founder at Succeedium
    TeamOne Google Sheets add-on for IBM Planning Analytics / TM1
    https://succeedium.com/teamone/
    ------------------------------

    Original Message