Input file has negative values

I apologize if this has already been covered.

I have an input file that can contain negative pricing amounts and am using a select tool to set the data type and size for the field. It is set to Type = Double.

The following conversion errors are received. The workflow has a Cleansing tool before and does the following:

1. Replace NULLS with Blanks (String Fields)

2. Replace NULLS with zeroes (Numeric Fields)

3. Remove Leading and Trailing Whitespace.

I'm not sure why i'm getting these errors? Has anyone ever dealt with this issue? Any ideas on how to clear it?

ConvError: Select (9): PRICINGAMOUNT: 2500.00- lost information in translation

ConvError: Select (9): PERCENTAGE: 0.0000 lost information in translation

ConvError: Select (9): PRICINGAMOUNT: 0.00 lost information in translation

ConvError: Select (9): PERCENTAGE: 100.0000 lost information in translation

ConvError: Select (9): PRICINGAMOUNT: 42500.00- lost information in translation

ConvError: Select (9): SURCHARGERATE: 62500.00- lost information in translation

ConvError: Select (9): PERCENTAGE: 100.0000- lost information in translation

The second issue happening is that the double data type is loosing the negative value. How can i keep the -42500 as the field value?

Thank you in advance for your help. I am so appreciative!

Error Message

Accepted answers

cmcclellan

@BuckeyeJane wrote:
How can i keep the -42500 as the field value?

This will help, as @Thableaus said you need to move the minus sign to the front by doing this:

if Right([PRICINGAMOUNT], 1) = "-"
then right([PRICINGAMOUNT],1) + left([PRICINGAMOUNT], Length([PRICINGAMOUNT])-1)
else [PRICINGAMOUNT]
endif

It could be the same issue with PERCENTAGE, but remember that 100 should really be 1 (so divide by 100). The data type could be causing the issue with 100.0000

All comments

Thableaus

Hi @BuckeyeJane

Could you share a sample of the data you're having these conversion errors?

Cheers,

BuckeyeJane

Yes - Below are some screen shots of the raw data. Thank you so much!

cmcclellan

@BuckeyeJane wrote:
How can i keep the -42500 as the field value?

This will help, as @Thableaus said you need to move the minus sign to the front by doing this:

if Right([PRICINGAMOUNT], 1) = "-"
then right([PRICINGAMOUNT],1) + left([PRICINGAMOUNT], Length([PRICINGAMOUNT])-1)
else [PRICINGAMOUNT]
endif

It could be the same issue with PERCENTAGE, but remember that 100 should really be 1 (so divide by 100). The data type could be causing the issue with 100.0000

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703