Problem with NUM function
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 48
- Joined: Fri Feb 29, 2008 1:09 am
- Location: Bangalore
Problem with NUM function
Hi all,
I am facing problem with NUM function,
My requirement is i have to trim the input value,after that i have to check if it is numeric or not.
for this i gave the derivation like this.
If Num(Trim(in_dataset.Member_Relationship_Cd)) then 1 else 0
when i use trim function along with NUM i am getting warning as
"Tfm_Stg1,0: Null string argument."
from soucre i am getting two spaces so if i trim those thing i will get empty then my output has to be one because empty is numeric value.
To overcome taht warning i tried removing trim function but that time my output is coimg as 0 because space is not a numeric.
can any one please help me.
Thanks for your help.
Regards
Swathi S
I am facing problem with NUM function,
My requirement is i have to trim the input value,after that i have to check if it is numeric or not.
for this i gave the derivation like this.
If Num(Trim(in_dataset.Member_Relationship_Cd)) then 1 else 0
when i use trim function along with NUM i am getting warning as
"Tfm_Stg1,0: Null string argument."
from soucre i am getting two spaces so if i trim those thing i will get empty then my output has to be one because empty is numeric value.
To overcome taht warning i tried removing trim function but that time my output is coimg as 0 because space is not a numeric.
can any one please help me.
Thanks for your help.
Regards
Swathi S
Thanks&Regards
S.Swathi
S.Swathi
-
- Participant
- Posts: 2
- Joined: Thu Aug 24, 2006 9:08 am
- Location: USA
Re: Problem with NUM function
Swathi,
Try something like this:
If Trim(in_dataset.Member_Relationship_Cd)="" Then 1 Else If Num(Trim(in_dataset.Member_Relationship_Cd)) then 1 else 0
Cheers,
K
Try something like this:
If Trim(in_dataset.Member_Relationship_Cd)="" Then 1 Else If Num(Trim(in_dataset.Member_Relationship_Cd)) then 1 else 0
Cheers,
K
Your assumption that Num would return true for an empty string is wrong.
should provide the result that you seem to be looking for.
Mike
Code: Select all
If Trim(in_dataset.Member_Relationship_Cd) = "" Then 1 Else Num(Trim(in_dataset.Member_Relationship_Cd))
Mike
-
- Participant
- Posts: 48
- Joined: Fri Feb 29, 2008 1:09 am
- Location: Bangalore
Hi Mike ,
Thanks for you replay
From datastage help i came to know that NUM function will return true if it is empty.
In the help its given like this.
Arg3 = " "
Boolean = Num(Arg3) ;* False (space is not numeric)
Arg4 = ""
Boolean = Num(Arg4) ;* True (empty string is numeric)
But when i tested it with empty value its returning false.
Regards
Swathi S
Thanks for you replay
From datastage help i came to know that NUM function will return true if it is empty.
In the help its given like this.
Arg3 = " "
Boolean = Num(Arg3) ;* False (space is not numeric)
Arg4 = ""
Boolean = Num(Arg4) ;* True (empty string is numeric)
But when i tested it with empty value its returning false.
Regards
Swathi S
-
- Participant
- Posts: 48
- Joined: Fri Feb 29, 2008 1:09 am
- Location: Bangalore
-
- Charter Member
- Posts: 64
- Joined: Sat Sep 17, 2005 10:42 am
try using the function 'Convert'.
svar1= If IsNull(Input.Col) Then '' Else Convert('0123456789',' ',Input.Col); -- Convert all numbers to spaces
svar2=Trim(svar1," ","A") -- Trim all spaces
If svar2 = '' Then Non-Numeric Else Numeric
I am using similar logic to clean junk characters that are coming from the source in ZIP CODE field, TELEPHONE fields to pass only numbers to the next stage STAN(QualityStage).
svar1= If IsNull(Input.Col) Then '' Else Convert('0123456789',' ',Input.Col); -- Convert all numbers to spaces
svar2=Trim(svar1," ","A") -- Trim all spaces
If svar2 = '' Then Non-Numeric Else Numeric
I am using similar logic to clean junk characters that are coming from the source in ZIP CODE field, TELEPHONE fields to pass only numbers to the next stage STAN(QualityStage).