Numeric or String??

Post Reply
Byron Hsu
Posts: 17
Joined: Mon Sep 29, 2008 11:36 pm
Version: 9.4 MR1
Excel Version: 2003
Location: Sydney

Numeric or String??

Post by Byron Hsu »

hi all,

is there a way to identify if a variable is a numeric or a string in a TI process?

I will be using it in a if statement, if the variable is a numeric value then do ABC, if the variable is a string value then do DEF.

Cheers,
Byron
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: Numeric or String??

Post by Alan Kirk »

Byron Hsu wrote:hi all,

is there a way to identify if a variable is a numeric or a string in a TI process?

I will be using it in a if statement, if the variable is a numeric value then do ABC, if the variable is a string value then do DEF.

Cheers,
Byron
What's the data source? If it's a view, look at the Help system for the Value_Is_String TI Process variable. This is useful if the dimension contains both N and S elements.

If that won't work for you, do you mean just whether the variable value can be REGARDED as being a numeric value? (Obviously you'd have to define the variable field as either Numeric or String, so you'd know ahead of time what it is. If that's the case, are you importing the field as a string and just want to know if the content can be treated as being numeric?)
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
Byron Hsu
Posts: 17
Joined: Mon Sep 29, 2008 11:36 pm
Version: 9.4 MR1
Excel Version: 2003
Location: Sydney

Re: Numeric or String??

Post by Byron Hsu »

Hi Alan,

The data source is a SQL DB. The column was set as a string value in the databse, I also defined the variable field as a String variable.

For example(Source data):

Product Name (prodnm)
012
ABC

What I want the TI process to do is
IF(prodnm is a numeric);
do ABC
ELSE(Prodnm is a string);
do DEF
ENDIF;
lotsaram
MVP
Posts: 3647
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Numeric or String??

Post by lotsaram »

My response was going to be the same as Alan's but he beat me to it.

If you have defined the field from your SQL table/query as a string variable and the field could potentially contain text or numeric data then you can't treat the variable as numeric in TI, you will have to assign a second variable. The simplest path would be to either use StringToNumber and see if it errors or not and test whether the minor error count has incremented. Or if you don't want to create error logs then your other choice is to substring your way through the string variable one character at a time, if you encounter anything other than a numeric, decimal point, comma or minus sign you have a string, otherwise you can go ahead and do the StringTo Number conversion.

Does that make sense to you or do you need more detail?
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: Numeric or String??

Post by Alan Kirk »

Byron Hsu wrote:Hi Alan,

The data source is a SQL DB. The column was set as a string value in the databse, I also defined the variable field as a String variable.

For example(Source data):

Product Name (prodnm)
012
ABC

What I want the TI process to do is
IF(prodnm is a numeric);
do ABC
ELSE(Prodnm is a string);
do DEF
ENDIF;
Yeah, I was afraid of that. Unfortunately that's where things get a little more complex.

There isn't (AFAIK) an equivalent to VBA's IsNumeric function in TI. Also, the NUMBR function is no help (using it to convert the variable to a numeric value the testing whether it's something other than 0) because if you had a field value like "1x5" it would still translate to a number, even though it's not.

The only thing that I can think of doing, and it is NOT a pretty solution, is this:
- Set a variable to a value of 'T' for True. This is a "flag" variable indicating that the variable value IS numeric.
- Get the length of the variable using the Long function;
- Create a While loop which runs from 1 to the value returned by the Long function;
- In that loop, use the Code function to determine the ASCII value of each character.
- If the Code value is NOT between 48 and 57 (the values for the characters 0 through to 9), 45 (a minus sign), 46 (a decimal point) or (possibly) 44 (a comma), then set the flag to 'F' and increment the counter to the Long value +1 to exit the loop. (This ensures that you loop through the minimum number of characters; if you find one non-numeric character, it's a waste of time testing the others.)

You can then use something like
If (Flag @='T');
# Do the numeric thing
Else;
# Do the string thing
EndIf;

Hopefully there's some neat new function which post-dates 8.2.12 which will remove the need to do that, but I don't have access to more recent help files at the moment and don't know if it if there is. Possibly someone else can give you a better suggestion, but that's the best I can do.
Last edited by Alan Kirk on Wed Jun 03, 2009 4:28 am, edited 1 time in total.
Reason: Ooops, left out the minus sign code.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: Numeric or String??

Post by Alan Kirk »

lotsaram wrote:My response was going to be the same as Alan's but he beat me to it.

If you have defined the field from your SQL table/query as a string variable and the field could potentially contain text or numeric data then you can't treat the variable as numeric in TI, you will have to assign a second variable. The simplest path would be to either use StringToNumber and see if it errors or not and test whether the minor error count has incremented. Or if you don't want to create error logs then your other choice is to substring your way through the string variable one character at a time, if you encounter anything other than a numeric, decimal point, comma or minus sign you have a string, otherwise you can go ahead and do the StringTo Number conversion.

Does that make sense to you or do you need more detail?
That's OK, you beat me to the [Post] button with the iterate the characters suggestion. :D

The errors idea was an interesting one; it's the sort of thing that's often done in VBA, except in cases like this you don't need to since VBA actually HAS an IsNumeric function. (Which TI probably should, but I doubt we'll ever see it.)
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
Byron Hsu
Posts: 17
Joined: Mon Sep 29, 2008 11:36 pm
Version: 9.4 MR1
Excel Version: 2003
Location: Sydney

Re: Numeric or String??

Post by Byron Hsu »

thanks all.
If the Code value is NOT between 48 and 57 (the values for the characters 0 through to 9), 45 (a minus sign), 46 (a decimal point) or (possibly) 44 (a comma), then set the flag to 'F' and increment the counter to the Long value +1 to exit the loop. (This ensures that you loop through the minimum number of characters; if you find one non-numeric character, it's a waste of time testing the others.)
Alan, actaully I have thought about to use this workaround before I posted the question, but thought maybe someone knows a better solution.
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: Numeric or String??

Post by Alan Kirk »

Byron Hsu wrote:thanks all.
If the Code value is NOT between 48 and 57 (the values for the characters 0 through to 9), 45 (a minus sign), 46 (a decimal point) or (possibly) 44 (a comma), then set the flag to 'F' and increment the counter to the Long value +1 to exit the loop. (This ensures that you loop through the minimum number of characters; if you find one non-numeric character, it's a waste of time testing the others.)
Alan, actaully I have thought about to use this workaround before I posted the question, but thought maybe someone knows a better solution.
Well, if it were Access I'd probably avail myself of the built-in IsNumeric function in the query and handle this on the source side. For example, if I wanted to know whether the field PL_Open in the table PriceLists contained something that was numeric in content:

Code: Select all

SELECT PriceLists.PL_Open, IsNumeric([PriceLists]![PL_Open]) AS ValueIsNumeric
FROM PriceLists;
This would give two variables in the data source. PL_Open would of course have to be brought in as a string (since it could be either), ValueIsNumeric as a number (which will be -1 if true or 0 if false). I could then have used:
If ( ValueIsNumeric = -1);
# Convert PL_Open to a value and do the numeric code
Else;
# Handle PL_Open as a string
EndIf;

Alas, I don't know SQL Server well enough (yet) to know whether it offers a similar functionality that you could use...
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
User avatar
Steve Rowe
Site Admin
Posts: 2407
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: Numeric or String??

Post by Steve Rowe »

You might be able to use the Numbr function, something like this

#Special case for leading zero, which would get dropped when using the Numbr function (I think)
If(subst(testString,1,1)@='0' , LongAdj=1, LongAdj=0);

#Is the length of the string the same after I convert it to a number?
If ( long(testString)=long( trim(str (Numbr(testStr),20,0))) + LongAdj);
#testString is numeric
Else;
#testString is not numeric

Endif;

The above test also assumes no decimal places in the testString.

HTH

(edit- added test for leading 0)
Technical Director
www.infocat.co.uk
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: Numeric or String??

Post by Alan Kirk »

Steve Rowe wrote:You might be able to use the Numbr function, something like this

#Special case for leading zero, which would get dropped when using the Numbr function (I think)
If(subst(testString,1,1)@='0' , LongAdj=1, LongAdj=0);

#Is the length of the string the same after I convert it to a number?
If ( long(testString)=long( trim(str (Numbr(testStr),20,0))) + LongAdj);
#testString is numeric
Else;
#testString is not numeric

Endif;

The above test also assumes no decimal places in the testString.

HTH

(edit- added test for leading 0)
There might also be a problem if the numeric string is formatted with comma separators, or has fixed decimal places. (I.e. the possibility of an unknown number of trailing zeroes, which would effectively vanish in the Numbr() conversion.)
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
User avatar
Steve Rowe
Site Admin
Posts: 2407
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: Numeric or String??

Post by Steve Rowe »

Agreed,
I was kinda guessing that the OP was testing for code alphanumerics that were all numeric rather than proper numbers.
Cheers,
Technical Director
www.infocat.co.uk
Byron Hsu
Posts: 17
Joined: Mon Sep 29, 2008 11:36 pm
Version: 9.4 MR1
Excel Version: 2003
Location: Sydney

Re: Numeric or String??

Post by Byron Hsu »

Code: Select all
SELECT PriceLists.PL_Open, IsNumeric([PriceLists]![PL_Open]) AS ValueIsNumeric
FROM PriceLists;
I have to say this solution is the easiest workaround so far. thanks Alan!!!

Cheers,
Byron
User avatar
Steve Rowe
Site Admin
Posts: 2407
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: Numeric or String??

Post by Steve Rowe »

Sorry to post on such an old post, just wanted to update this since my approach doesn't work at all!

This is because the numbr function includes "e" in it's definition of a number (like 1E+03=1000).
So if you were to test something
"100 Why doesn't this work"
the numbr function returms "100e" which it can't resolve into a number since it needs another integer after the e from "doesn't". This then throws an error into the TI log. This is sort of OK since I want to reject the record anyway. I don't really like to have TIs that will generate errors in the normal course of operation as it makes it hard to notice when something actually goes wrong.

This leaves me with iteration through the element name, which is a bit tedious and drags a little on the speed of my TI process.

Note that my requirement is to test if an element name is numeric or not. I'm not testing for any of the punctuation of a formatted number.

Code: Select all

#element we are testing
elTest=MIcode;
#length of the element
mxChar=long(elTest);
#set counters
ixChar=1;
fail=0;
While ( ixChar<mxChar & fail=0);
    testASCII=Code(elTest,ixChar); 
#test if the ascii code is in the range for a numeric value. 48 is "0" and 57 is "9"
     If ( ~ (testASCII>=48 & testASCII<=57));
         fail=1;
    endif;
    ixChar=ixChar+1;
End;

If (fail=1);
   ItemSkip;
EndIf;
Note that I could put the ItemSkip where I set fail=1 in the While loop but I tend to avoid breaking out of while loops since I’ve had some odd bugs in this area before. Things like the endif of the next if after you break out of while loop being skipped over.

Anyway in the end it seems quite hard to test if an element is numeric without filling your log file with false positive error messages.
Technical Director
www.infocat.co.uk
Post Reply