StringToNumber does not use locale decimal delimiter
- vovanenok
- Posts: 88
- Joined: Mon Jun 23, 2014 4:54 pm
- OLAP Product: TM1
- Version: 2.0.9
- Excel Version: Office 365
- Location: Toronto, Canada
- Contact:
StringToNumber does not use locale decimal delimiter
Hi all
I have an issue when some user with French Windows Regional Settings runs a process (actually clicks a button in TM1Web to run that process). The process exports data to a tab delimited file, then another process imports it back to TM1 cube.
Initially I used NumberToString function to export numeric values.
As per documentation both NumberToString and StringToNumber should use the decimal separator for the current user locale. However the process was failing on StringToNumber saying about an invalid real number: 1,122
Is it a bug with StringToNumber? Why id doesn't use the same delimiter as NumberToString?
I decided to workaround that using NumberToStringEx function and explicitly specifying decimal "." and thousand "," separators. It started working but then import failed for very small negative numbers, because NumberToStringEx function ,for example, converts 0.00 to empty string '' or -0.00 to '-' string and later StringToNumber function fails importing that.
The view is zero suppressed but I get those tiny values due to rounding issue (similar issue was discussed here http://www.tm1forum.com/viewtopic.php?f=3&t=12143).
I could load the data directly from source to target cube slice (without import), but sometimes I use my own generic processes to transform the target file before loading it to TM1 and want that to work through export file.
Thanks in advance!
I have an issue when some user with French Windows Regional Settings runs a process (actually clicks a button in TM1Web to run that process). The process exports data to a tab delimited file, then another process imports it back to TM1 cube.
Initially I used NumberToString function to export numeric values.
As per documentation both NumberToString and StringToNumber should use the decimal separator for the current user locale. However the process was failing on StringToNumber saying about an invalid real number: 1,122
Is it a bug with StringToNumber? Why id doesn't use the same delimiter as NumberToString?
I decided to workaround that using NumberToStringEx function and explicitly specifying decimal "." and thousand "," separators. It started working but then import failed for very small negative numbers, because NumberToStringEx function ,for example, converts 0.00 to empty string '' or -0.00 to '-' string and later StringToNumber function fails importing that.
The view is zero suppressed but I get those tiny values due to rounding issue (similar issue was discussed here http://www.tm1forum.com/viewtopic.php?f=3&t=12143).
I could load the data directly from source to target cube slice (without import), but sometimes I use my own generic processes to transform the target file before loading it to TM1 and want that to work through export file.
Thanks in advance!
-
- Posts: 78
- Joined: Wed Jul 31, 2013 4:32 am
- OLAP Product: Cognos TM1, EP, Analyst
- Version: 10.2.2
- Excel Version: 2013
- Location: Sydney AU
Re: StringToNumber does not use locale decimal delimiter
I tend to use NUMBR function for converting strings to numbers.
Is the transfer into text file necessary? For example, cross TM1 instance? Is cube to cube an option?
The risk with this function is, if for some reason that one of your user decided to set the locale differently, eg. using "," as decimal and "." as thousand separator, you will get wrong results.The string you want to convert to a number. All characters other than '0' through '9', '+', '-', '.', and 'E' are ignored.
Is the transfer into text file necessary? For example, cross TM1 instance? Is cube to cube an option?
MK
-
- MVP
- Posts: 1815
- Joined: Mon Dec 05, 2011 11:51 am
- OLAP Product: Cognos TM1
- Version: PA2.0 and most of the old ones
- Excel Version: All of em
- Location: Manchester, United Kingdom
- Contact:
Re: StringToNumber does not use locale decimal delimiter
The issue is that with TM1 web the process is not able tonpick anything up from the clients PC... in its mind the client PC is the web server.
I did something similar to this recently and gave the user a delimiter dropdown on the web screen and passed it to the TI as a parameter - this was then used in the datasource variable functions.
Same for the re-upload TI.
I did something similar to this recently and gave the user a delimiter dropdown on the web screen and passed it to the TI as a parameter - this was then used in the datasource variable functions.
Same for the re-upload TI.
Declan Rodger
- vovanenok
- Posts: 88
- Joined: Mon Jun 23, 2014 4:54 pm
- OLAP Product: TM1
- Version: 2.0.9
- Excel Version: Office 365
- Location: Toronto, Canada
- Contact:
Re: StringToNumber does not use locale decimal delimiter
As I explained before, in this particular case I can load data directly to my target cube. But in some cases I need to use file export.babytiger wrote:Is the transfer into text file necessary? For example, cross TM1 instance? Is cube to cube an option?
There is some inconsistency in TM1, using French format I test:declanr wrote:The issue is that with TM1 web the process is not able tonpick anything up from the clients PC... in its mind the client PC is the web server.
I did something similar to this recently and gave the user a delimiter dropdown on the web screen and passed it to the TI as a parameter - this was then used in the datasource variable functions.
Same for the re-upload TI.
Code: Select all
AsciiOutput(lf, DatasourceASCIIDecimalSeparator);
AsciiOutput(lf, NumberToString(1234.1234));
Code: Select all
.
1234,1234
Another inconsistency is that I can set a file delimiter for AsciiOutput using DatasourceASCIIDelimiter and I would expect to be able to override decimal separator using DatasourceASCIIDecimalSeparator, but I cannot
- vovanenok
- Posts: 88
- Joined: Mon Jun 23, 2014 4:54 pm
- OLAP Product: TM1
- Version: 2.0.9
- Excel Version: Office 365
- Location: Toronto, Canada
- Contact:
Re: StringToNumber does not use locale decimal delimiter
NUMBR fails converting this string: "-"babytiger wrote:I tend to use NUMBR function for converting strings to numbers.
The string you want to convert to a number. All characters other than '0' through '9', '+', '-', '.', and 'E' are ignored.
-
- MVP
- Posts: 2831
- Joined: Tue Feb 16, 2010 2:39 pm
- OLAP Product: TM1, Palo
- Version: Beginning of time thru 10.2
- Excel Version: 2003-2007-2010-2013
- Location: Atlanta, GA
- Contact:
Re: StringToNumber does not use locale decimal delimiter
TI processes do not interact with the user for anything other than status updates so they cannot use anything from the user's locale (as was stated earlier by Declan). They use the locale of the server.vovanenok wrote:So DatasourceASCIIDecimalSeparator does not use decimal separator from user's locale, but NumberToString does
- vovanenok
- Posts: 88
- Joined: Mon Jun 23, 2014 4:54 pm
- OLAP Product: TM1
- Version: 2.0.9
- Excel Version: Office 365
- Location: Toronto, Canada
- Contact:
Re: StringToNumber does not use locale decimal delimiter
so why the next:tomok wrote:TI processes do not interact with the user for anything other than status updates so they cannot use anything from the user's locale (as was stated earlier by Declan). They use the locale of the server.vovanenok wrote:So DatasourceASCIIDecimalSeparator does not use decimal separator from user's locale, but NumberToString does
Code: Select all
AsciiOutput(lf, NumberToString(1234.1234));
Code: Select all
1234,1234
Code: Select all
1234.1234
On both TM1 and TM1Web servers the locale is set to English (Canada), decimal symbol is set to '.'
I have TM1 10.2 FP1. To reproduce that example you can create a process containing AsciiOutput(lf, NumberToString(1234.1234)); in prolog, create a perpectives report with a single button to run that process, try it two times switching the mentioned locales before opening TM1Web.
-
- MVP
- Posts: 3103
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: StringToNumber does not use locale decimal delimiter
Hi all,
TM1 10.2.2 FP6 here. I can confirm the findings.
TI process:
Results:
I run the process on the server with US settings, it gives me 1234.1234
I run the same process on my client with Dutch settings, and it gives me 1234,1234
See screenshots. The client with Dutch regional settings is on the left on the first picture, and also on the 2nd picture.
The server with US settings is on the right on the first picture, and also on the 3rd picture.
TM1 10.2.2 FP6 here. I can confirm the findings.
TI process:
Code: Select all
lf = 'test.txt';
AsciiOutput(lf, NumberToString(1234.1234));
I run the process on the server with US settings, it gives me 1234.1234
I run the same process on my client with Dutch settings, and it gives me 1234,1234
See screenshots. The client with Dutch regional settings is on the left on the first picture, and also on the 2nd picture.
The server with US settings is on the right on the first picture, and also on the 3rd picture.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
- vovanenok
- Posts: 88
- Joined: Mon Jun 23, 2014 4:54 pm
- OLAP Product: TM1
- Version: 2.0.9
- Excel Version: Office 365
- Location: Toronto, Canada
- Contact:
Re: StringToNumber does not use locale decimal delimiter
Today I opened a PMR and had even a WebEx session with IBM. They agreed it seems like a bug with locales. Regarding DatasourceASCIIDecimalSeparator they offered to create a request for enhancement to support this variable in AsciiOutput (like it works with DatasourceASCIIDelimiter).