check if a parameter value is numeric

Post Reply
manu0521
Posts: 82
Joined: Wed Nov 26, 2014 8:32 pm
OLAP Product: TM1
Version: 9.5
Excel Version: 2010

check if a parameter value is numeric

Post by manu0521 » Wed Oct 02, 2019 9:09 pm

Pass a cell value from excel to tm1 ti process.

User can enter any value and submit an action button , in ti ,I have to check if the passed value is numeric .

Is there a function in tm1 which can check if the parameter value is numeric.

Thanks,

tomok
MVP
Posts: 2739
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: check if a parameter value is numeric

Post by tomok » Thu Oct 03, 2019 1:19 am

manu0521 wrote:
Wed Oct 02, 2019 9:09 pm
User can enter any value
Not if you apply data validation in Excel.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

User avatar
macsir
Community Contributor
Posts: 701
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365
Contact:

Re: check if a parameter value is numeric

Post by macsir » Fri Oct 04, 2019 4:25 am

Better than nothing :D

Code: Select all

SCAN( SUBST( vsVar, 1, 1 ), '0123456789' ) <> 0
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

Alex Leung
Posts: 7
Joined: Fri May 24, 2019 1:23 am
OLAP Product: Planning Analytics
Version: 2.0.6
Excel Version: Excel 2013 32-bit

Re: check if a parameter value is numeric

Post by Alex Leung » Tue Jun 30, 2020 3:58 am

Set the parameter as string type first, then add checking as below:

Code: Select all

IF( vsVar @= NumberToString( NumBr ( vsVar)) );
#Is Numeric
Else;
#Not;
EndIf;
Alex

Mark RMBC
Community Contributor
Posts: 232
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: check if a parameter value is numeric

Post by Mark RMBC » Tue Jun 30, 2020 9:35 am

Hi,

I would think Tomok's suggestion is the way to go, but

Cant you just do the check in Excel, so have a formula along the lines of:

=IFERROR(A1*1,0)
=IF(A2=0,0,1)

Then have a parameter in the TI called pIsNumeric (1 or 0) and have the action button point at this cell?

regards,
Mark

User avatar
gtonkin
MVP
Posts: 805
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.3
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: check if a parameter value is numeric

Post by gtonkin » Tue Jun 30, 2020 3:08 pm

Or you could use =ISNUMBER() assuming you are not using TM1 Web.

Mark RMBC
Community Contributor
Posts: 232
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: check if a parameter value is numeric

Post by Mark RMBC » Tue Jun 30, 2020 4:17 pm

Yes, I originally had isnumber in my comment but then thought it is probably for the web so changed it!

Andy Key
MVP
Posts: 345
Joined: Wed May 14, 2008 1:37 pm
OLAP Product: TM1
Version: 2.5 to PA
Excel Version: Lots
Location: Sydney
Contact:

Re: check if a parameter value is numeric

Post by Andy Key » Wed Jul 01, 2020 2:31 am

You still need to change it if you're on 2.0.6 or earlier.

IFERROR only got TM1Web support in 2.0.7.
Andy Key

Mark RMBC
Community Contributor
Posts: 232
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: check if a parameter value is numeric

Post by Mark RMBC » Wed Jul 01, 2020 7:50 am

Yes, and in that case just use if iserror!

regards, Mark

User avatar
orlando
Community Contributor
Posts: 134
Joined: Fri Aug 04, 2017 8:27 am
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365

Re: check if a parameter value is numeric

Post by orlando » Fri Jul 03, 2020 10:41 am

manu0521 wrote:
Wed Oct 02, 2019 9:09 pm

Is there a function in tm1 which can check if the parameter value is numeric.

here's my idea.
Why don't you store the value in a cube (eg. Value_Input) per user.
For example, the cube could have the dimensions }Client and Measure_Input.
The elements for the input are created in the dimension Measure_Input.
Instead of passing the value to the process, the user and the element from Measure_Input are passed to the process.
The process then collects the value form the cube.
This ensures that no nonsense goes to the process and you can see what exactly the user entered last.
The input cube can of course have other dimensions, it was just a quick idea.

best regards
orlando

Post Reply