Format 195,231 (char) to 195231 (Numeric)
Moderators: chulett, rschirm, roy
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.chulett wrote:Still think math would do the trick...
Give that a shot.Code: Select all
If Len(Trim((MYLINK.COST)) = 0 Then 0 Else MYLINK.COST * 1
Thanks in advance!
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.
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
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.pnchowdary wrote:Hi Craig,
That was a nice little trick . Can't we use the Real function in this scenario?
Trim(Ereplace(yourcolumn,',',''))
and check this result is a Num or not.
Suresh
i used following transformation!Sainath.Srinivasan wrote:You need to take the rightmost char to check whether it is a sign. Try using what you have learnt earlier.
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
Thanks
-
- Participant
- Posts: 232
- Joined: Sat May 07, 2005 2:49 pm
- Location: USA