Page 1 of 1
TM1 Rule issue using NUMBR() function
Posted: Mon Jul 22, 2024 3:17 pm
by 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
Re: TM1 Rule issue using NUMBR() function
Posted: Mon Jul 22, 2024 8:54 pm
by Wim Gielis
mlorini wrote: ↑Mon Jul 22, 2024 3:17 pm
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.
Re: TM1 Rule issue using NUMBR() function
Posted: Tue Jul 23, 2024 7:47 am
by 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'] ) ;
Re: TM1 Rule issue using NUMBR() function
Posted: Tue Jul 23, 2024 8:07 am
by lotsaram
HighKeys wrote: ↑Tue Jul 23, 2024 7:47 am
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.
Re: TM1 Rule issue using NUMBR() function
Posted: Tue Jul 23, 2024 12:56 pm
by 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.
Re: TM1 Rule issue using NUMBR() function
Posted: Tue Jul 23, 2024 1:26 pm
by Wim Gielis
mlorini wrote: ↑Tue Jul 23, 2024 12:56 pm
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) ?
Re: TM1 Rule issue using NUMBR() function
Posted: Tue Jul 23, 2024 1:29 pm
by tomok
mlorini wrote: ↑Tue Jul 23, 2024 12:56 pm
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.
Re: TM1 Rule issue using NUMBR() function
Posted: Tue Jul 23, 2024 1:43 pm
by 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
Re: TM1 Rule issue using NUMBR() function
Posted: Tue Jul 23, 2024 1:47 pm
by 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.