Format 195,231 (char) to 195231 (Numeric)

Titto · Post by **Titto** » Thu Sep 08, 2005 3:38 pm

Craig, It worked out!!

Thanks!

chulett · Post by **chulett** » Thu Sep 08, 2005 4:23 pm

pnchowdary wrote:Can't we use the Real function in this scenario?

Not sure as I haven't had a need to use that one before. Might be something to try for grins.

chulett · Post by **chulett** » Thu Sep 08, 2005 4:23 pm

Titto wrote:It worked out!!

Yah!

Titto · Post by **Titto** » Fri Sep 09, 2005 8:36 am

chulett wrote:Still think math would do the trick...
Code: Select all
If Len(Trim((MYLINK.COST)) = 0 Then 0 Else MYLINK.COST * 1
Give that a shot.

Craig, still have the problem

.. In my input data comes like 234- (negative) values, above tric is not working out!! and if i don't trim(MYLINK.COST,',','A')*1 then it is not transforming values greater than thousands like 195,195. But the problem is with negative numbers, how do i handle them.
Thanks in advance!

chulett · Post by **chulett** » Fri Sep 09, 2005 8:42 am

What is the datatype/sizes of both fields in your job? What exactly happens to negative numbers?

Titto · Post by **Titto** » Fri Sep 09, 2005 9:07 am

Code: Select all

Source Date(CHAR(15))     Target Data (DECIMAL (15,2))
          123,123                           123123.00  (tric works)
          123                                  123.00       (tric works)
          231- (negative)                 0.00      (tric doesn't work but it suppose to be 231.00-

Right now with the tric all negative numbers are tranformed to 0.00.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Fri Sep 09, 2005 9:50 am

You need to take the rightmost char to check whether it is a sign. Try using what you have learnt earlier.

ysureshv · Post by **ysureshv** » Fri Sep 09, 2005 2:06 pm

pnchowdary wrote:Hi Craig,

That was a nice little trick . Can't we use the Real function in this scenario?

If you are sure you have only ' , ' in your column , you can ereplace that with null and check for Num of that field and insert in the Num field.

Trim(Ereplace(yourcolumn,',',''))
and check this result is a Num or not.

Suresh

Titto · Post by **Titto** » Mon Sep 12, 2005 10:37 am

Sainath.Srinivasan wrote:You need to take the rightmost char to check whether it is a sign. Try using what you have learnt earlier.

i used following transformation!

Code: Select all

IF Len(Trim(MYLINK.COST)) = 0 
   Then 0 
Else 
   IF Right(Trim(MYLINK.COST,',','A'),1) = '-' 
   Then '-':(Trim(Trim(MYLINK.COST,',','A'),'-','A'))
Else Trim(MYLINK.COST,',','A')*1

it is working fine, is there any other easy way to achieve this..

Thanks

pnchowdary · Post by **pnchowdary** » Mon Sep 12, 2005 1:58 pm

Hi Titto,

I believe that, it is as easy as it gets, to meet your requirement.