Input file has negative values
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
Solved! Go to Solution.
- Labels:
- Error Message
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Yes - Below are some screen shots of the raw data. Thank you so much!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
The negative sign is in front of the decimal number. You need to use a Formula Tool to properly put it before the number itself.
Could you share a file with a sample of the data? You can simply leave the fields you're having trouble with, you don't need to share your sensitive data.
Cheers,
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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