Page 1 of 1
Numeric or String??
Posted: Wed Jun 03, 2009 3:46 am
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
Re: Numeric or String??
Posted: Wed Jun 03, 2009 3:56 am
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?)
Re: Numeric or String??
Posted: Wed Jun 03, 2009 4:10 am
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;
Re: Numeric or String??
Posted: Wed Jun 03, 2009 4:22 am
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?
Re: Numeric or String??
Posted: Wed Jun 03, 2009 4:24 am
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.
Re: Numeric or String??
Posted: Wed Jun 03, 2009 4:26 am
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.
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.)
Re: Numeric or String??
Posted: Wed Jun 03, 2009 4:44 am
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.
Re: Numeric or String??
Posted: Wed Jun 03, 2009 6:10 am
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...
Re: Numeric or String??
Posted: Wed Jun 03, 2009 9:42 am
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)
Re: Numeric or String??
Posted: Wed Jun 03, 2009 10:10 am
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.)
Re: Numeric or String??
Posted: Wed Jun 03, 2009 10:27 am
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,
Re: Numeric or String??
Posted: Tue Jun 09, 2009 1:36 am
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
Re: Numeric or String??
Posted: Tue Aug 18, 2009 11:58 am
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.