TM1 Rule issue using NUMBR() function

[mlorini]

I am experiencing the following issue with a rule file's NUMBR() function.

This rule works just fine it return the numeric value present in string 'S Value'
[ 'N Value' ] = N: NUMBR ( DB( 'Test' , !Test , 'S Value' )) ;

This rule does not works, it returns #N/A for any numeric value present in string 'S Value'
[ 'N Value' ] = N: NUMBR ( 'S Value' ) ;

Has anyone seen this odd TM1 behavior yet?

Thanks

[Wim Gielis]

I am experiencing the following issue with a rule file's NUMBR() function.

This rule works just fine it return the numeric value present in string 'S Value'
[ 'N Value' ] = N: NUMBR ( DB( 'Test' , !Test , 'S Value' )) ;

This rule does not works, it returns #N/A for any numeric value present in string 'S Value'
[ 'N Value' ] = N: NUMBR ( 'S Value' ) ;

Has anyone seen this odd TM1 behavior yet?

Thanks

Under the 2nd option, you are trying to convert the text S Value into a number. It is not what you think (a measure containing stings that can easily be converted to numbers). At least this is what you think are trying to accomplish and where it goes wrong.

[HighKeys]

For me it looks like your 2nd Example is using the 's Value' Element from the same Cube, in that case you would need to write the rule like this:

Code: Select all

[ 'N Value' ] = N: NUMBR ( ['S Value'] ) ;

[lotsaram]

For me it looks like your 2nd Example is using the 's Value' Element from the same Cube, in that case you would need to write the rule like this:

Code: Select all

[ 'N Value' ] = N: NUMBR ( ['S Value'] ) ;

Nope that won't work. The square bracket shorthand notation is only valid for numeric values. To retrieve a string value the full DB() notation must be used.

Code: Select all

[ 'N Value' ] = N: NUMBR ( DB('cubeName', 'Fixed Ref1', 'Fixed Ref2', !DynamicRef1, !DynamicRef2, 'S Value') ) ;

To get back to the OP's original question ...

This rule does not works, it returns #N/A for any numeric value present in string 'S Value'
[ 'N Value' ] = N: NUMBR ( 'S Value' ) ;

Has anyone seen this odd TM1 behavior yet?

I think Wim already answered this. In the example above 'S Value' is not a variable, it is a hardcoded literal string. As the string contains no numbers there is no numeric result. Therefore it isn't odd behavior but quite expected and mundane behavior.
On the other hand ...
NUMBR ( 'S Va1ue' )
... would return a value of 1.

[mlorini]

Wim Gielis, I'm having trouble understanding your answer. I believe I may not have explained my issue clearly. I'm seeking a clear explanation and a solution to the problem with the measures in the test cube.
My Test cube has two measures: one is named' S Value' and is a string, and the other is named 'N Value' and is numeric. (please check the attached screenshot) .
The rule [ 'N Value' ] = N: NUMBR (' S Value' ) is expected to convert the numeric value entered in' S Value' and assign it to the element 'N Value'.
Screenshot shows that if I enter 'S Value' = 5 I get 'N Value' = '#N/A'

Also,
[ 'N Value' ] = N: NUMBR (['S Value']) ; generate a syntax error.

[Wim Gielis]

Wim Gielis, I'm having trouble understanding your answer. I believe I may not have explained my issue clearly. I'm seeking a clear explanation and a solution to the problem with the measures in the test cube.
My Test cube has two measures: one is named' S Value' and is a string, and the other is named 'N Value' and is numeric. (please check the attached screenshot) .
The rule [ 'N Value' ] = N: NUMBR (' S Value' ) is expected to convert the numeric value entered in' S Value' and assign it to the element 'N Value'.
Screenshot shows that if I enter 'S Value' = 5 I get 'N Value' = '#N/A'

Also,
[ 'N Value' ] = N: NUMBR (['S Value']) ; generate a syntax error.

Sure it gives a syntax error.

This rule works just fine it return the numeric value present in string 'S Value'
[ 'N Value' ] = N: NUMBR ( DB( 'Test' , !Test , 'S Value' )) ;

This rule does not works, it returns #N/A for any numeric value present in string 'S Value'
[ 'N Value' ] = N: NUMBR ( 'S Value' ) ;

If the first one works, why don't you stick with it ? It's correct.
What is it that makes you think you should use the second option (which does not work) ?

[tomok]

Wim Gielis, I'm having trouble understanding your answer. I believe I may not have explained my issue clearly. I'm seeking a clear explanation and a solution to the problem with the measures in the test cube.
My Test cube has two measures: one is named' S Value' and is a string, and the other is named 'N Value' and is numeric. (please check the attached screenshot) .
The rule [ 'N Value' ] = N: NUMBR (' S Value' ) is expected to convert the numeric value entered in' S Value' and assign it to the element 'N Value'.
Screenshot shows that if I enter 'S Value' = 5 I get 'N Value' = '#N/A'

Also,
[ 'N Value' ] = N: NUMBR (['S Value']) ; generate a syntax error.

Please go back and read lotsaram's post. It answers your question completely.

[mlorini]

Well,
I was trying to apply some performance optimization to our rule. The following article explain that Replace DB with [‘element’] there is a 40% performance gain.

https://code.cubewise.com/blog/7-tips-t ... ics-rules/

Thanks

[Wim Gielis]

Then the article could mention that this shorter syntax does not apply to String cells.
I.e., where the element in the last dimension of the cube is of type String.