StringToNumber does not use locale decimal delimiter

Post Reply
User avatar
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

Post 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!
----------
TeamOne Google Sheets add-on for IBM Planning Analytics
Let's connect on LinkedIn
babytiger
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

Post 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?
MK
declanr
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

Post 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.
Declan Rodger
User avatar
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

Post 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:

Code: Select all

.
1234,1234
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
----------
TeamOne Google Sheets add-on for IBM Planning Analytics
Let's connect on LinkedIn
User avatar
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

Post 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: "-"
----------
TeamOne Google Sheets add-on for IBM Planning Analytics
Let's connect on LinkedIn
tomok
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

Post 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.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
User avatar
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

Post 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:

Code: Select all

1234,1234
and for a user with English (Canada) regional format setting gives me:

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.
----------
TeamOne Google Sheets add-on for IBM Planning Analytics
Let's connect on LinkedIn
Wim Gielis
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

Post 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
00.PNG (118 KiB) Viewed 6260 times
01.PNG
01.PNG (3.24 KiB) Viewed 6260 times
02.PNG
02.PNG (3.48 KiB) Viewed 6260 times
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
User avatar
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

Post 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).
----------
TeamOne Google Sheets add-on for IBM Planning Analytics
Let's connect on LinkedIn
Post Reply