Page 1 of 1
StringToNumber does not use locale decimal delimiter
Posted: Thu Oct 20, 2016 8:24 pm
by vovanenok
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!
Re: StringToNumber does not use locale decimal delimiter
Posted: Thu Oct 20, 2016 10:38 pm
by babytiger
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.
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.
Is the transfer into text file necessary? For example, cross TM1 instance? Is cube to cube an option?
Re: StringToNumber does not use locale decimal delimiter
Posted: Thu Oct 20, 2016 11:15 pm
by declanr
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.
Re: StringToNumber does not use locale decimal delimiter
Posted: Fri Oct 21, 2016 2:11 pm
by vovanenok
babytiger wrote:Is the transfer into text file necessary? For example, cross TM1 instance? Is cube to cube an option?
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.
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.
There is some inconsistency in TM1, using French format I test:
Code: Select all
AsciiOutput(lf, DatasourceASCIIDecimalSeparator);
AsciiOutput(lf, NumberToString(1234.1234));
returns:
So DatasourceASCIIDecimalSeparator does not use decimal separator from user's locale, but
NumberToString does, which looks like an evident bug
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
Re: StringToNumber does not use locale decimal delimiter
Posted: Fri Oct 21, 2016 2:49 pm
by vovanenok
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.
NUMBR fails converting this string: "-"
Re: StringToNumber does not use locale decimal delimiter
Posted: Fri Oct 21, 2016 3:13 pm
by tomok
vovanenok wrote:So DatasourceASCIIDecimalSeparator does not use decimal separator from user's locale, but NumberToString does
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.
Re: StringToNumber does not use locale decimal delimiter
Posted: Fri Oct 21, 2016 5:16 pm
by vovanenok
tomok wrote:vovanenok wrote:So DatasourceASCIIDecimalSeparator does not use decimal separator from user's locale, but NumberToString does
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.
so why the next:
Code: Select all
AsciiOutput(lf, NumberToString(1234.1234));
for a user with French (Canada) regional format setting gives me:
and for a user with English (Canada) regional format setting gives me:
?
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.
Re: StringToNumber does not use locale decimal delimiter
Posted: Fri Oct 21, 2016 11:35 pm
by Wim Gielis
Hi all,
TM1 10.2.2 FP6 here. I can confirm the findings.
TI process:
Code: Select all
lf = 'test.txt';
AsciiOutput(lf, NumberToString(1234.1234));
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.
- 00.PNG (118 KiB) Viewed 8330 times
- 01.PNG (3.24 KiB) Viewed 8330 times
- 02.PNG (3.48 KiB) Viewed 8330 times
Re: StringToNumber does not use locale decimal delimiter
Posted: Sat Oct 22, 2016 12:23 am
by vovanenok
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).