Bringing numeric cell value as it is from the database

[ViRa]

This is regarding bringing a numeric cell value exactly as it appears in the data source (with the decimal precision).

The measure element 'SALES_TAX_AMT' is numeric and has a format with 3 decimals in the ODBC data source. Eg. '35.832'.
Since I need to output this value in to a text file, I convert this element in to string data. While converting, I used trim () to get rid of the trailing spaces and str(). With str(), I need to pass on the number of decimal places to be included as a parameter.

nSALES_TAX_AMT = CellGetN('Cube name', el1, el2, el3, SALES_TAX_AMT);
sSALES_TAX_AMT = trim(str(nSALES_TAX_AMT, 10, 3);

However, the decimal precision on back end might change anytime. It could change from 3 to 5 decimals. In such cases, I would have to edit the parameter and replace 3 with 5.

I wanted to know is there a way to capture the format exactly as it appears everytime the TI process is run without having to update the STR()?

Thank you for your time and help.

[Guillaume Galtier]

The measure element 'SALES_TAX_AMT' is numeric and has a format with 3 decimals in the ODBC data source. Eg. '35.832'.
Since I need to output this value in to a text file, I convert this element in to string data. While converting, I used trim () to get rid of the trailing spaces and str(). With str(), I need to pass on the number of decimal places to be included as a parameter.
.

Hi ViRa,

can't you simply use the NumberToString function ?

[tomok]

However, the decimal precision on back end might change anytime. It could change from 3 to 5 decimals.

'm not sure what you mean by this. Do you mean the precision for all records could change or do you mean just for certain records? If it's all records then you could just make the decimal precision a parameter of the TI process and then pass it in:

Code: Select all

sSALES_TAX_AMT = trim(str(nSALES_TAX_AMT, 10, pPrecision);

If it's only for certain records then I don't know how you could know that other than to add another element to your measure dimension to store sSALES_TAX_AMT as a string just as it comes in from ODBC and skip your TI process conversion. BTW, doing this would make the first idea not necessary.

[tomok]

can't you simply use the NumberToString function ?

This would only work if TM1 stored that data exactly as it came in from the original ODBS data source. As we all know, the 35.832 might be stored in TM1 as 35.832000001 due to the way TM1 stores numbers. To get back to the 35.832 you would have to round it to three decimal places before exporting, leaving you in the same quandary.

[Mark RMBC]

Not sure why you want this either but if you want to know the precision couldn't you compare the value to the INT value,

so something like:

vValue= long(NumbertoString(27875.3206))-1;

This equals 9

vINT = long(numbertostring(int(27875.3206)));

This equals 5.

take vINT from vValue and you have the precision

regards, Mark

[ViRa]

Thanks Tom, Mark and Guillaume for your reply.
The reason I need to know if TM1 can dynamically fetch the numeric data as it is from the DB because at one point the format for a value changed in the backend and I did not know about it. Initially it was 5 decimal places and in the str () I coded the length of decimals as 5. Then when it changed to 3 in the back end, the updated values from TM1 were appending the digits with 2 zeros to fill up the criteria of 5 decimal places that I had in the Str (). I had to update the code in order for the values to be accurate.

I did try storing the SALES_TAX_AMT as String and using NumbertoString () but since it is a numeric value in the DB the output resulted in some numbers being displayed in scientific format. Hence I went back to retaining the format as it is in the DB.

Do you mean the precision for all records could change or do you mean just for certain records?

It's for all the records. Tom, you mean I store the precision as 5 in a parameter and pass it on to str () ? In case the precision changed to 6 tomorrow I would still have to update the code to change this value from 5 to 6...am I correct?

Appreciate your time and response.

[declanr]

Use Tomok's suggestion but have a second TI that queries the database schema to find out the precision setting for the relevant column... that TI can update a control cube with the value or just use the value and call the original TI from its data tab.

[babytiger]

I feel you are coming from a wrong angle here. I don't see a valid reason for getting TM1 to dictate a file to meet your DB's requirements.

IMO, I think the file should contain data that matches exactly to TM1, without further massaging on the formatting and precision. In this case, NumberToString may be useful. And update the relevant SQL to do the decimal precision.

I feel there's a bit of patching there.

[declanr]

I feel you are coming from a wrong angle here. I don't see a valid reason for getting TM1 to dictate a file to meet your DB's requirements.

IMO, I think the file should contain data that matches exactly to TM1, without further massaging on the formatting and precision. In this case, NumberToString may be useful. And update the relevant SQL to do the decimal precision.

I feel there's a bit of patching there.

The reason I gather is the desired avoidance of TM1 binary floating point algorithm's "unusual" numbers... which I am sure has given all of us a few headaches before.

[Steve Rowe]

Is there an argument to leave this to data type of the destination DB which I assume must by relational and hence have good control over data types?
If the destination is TM1 then there is no point (geddit!) since floating point issues will reoccur once the data is loaded.