Strange behaviour with negative numbers rendering to Excel
- 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
When rendering to excel I get 'number formatted as text' errors for some negative numbers see below:
The -92,477 is not detected as an error but the other numbers are.
When I look in the Excel cell for the -92,477 i see -92476.8
When I look in the Excel cell for the -92,846 i see -92,846
Notice how the cell that errors out contains a comma and is rounded, this is the same for the other numbers resulting in number as text errors.
Further info, I had thought rounding was the issue, prior to this the numbers had up to 8 decimal places, and when that was the case only the -100,279 was erroring.
I added 0.01 rounding to the formulas that produce these values and now even more (but not all) negative numbers are erroring this way.
Any ideas?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi,
Alteryx sees 1,000 or (100) as a string. Put a select tool before the output to see what data type is assigned to the fields you think are numbers. The Type field will show if the value is a string or number format. If string, strip out the non-numeric characters - the commas and the parentheses. You can use Regex tool or a formula. My attached example nests the replace function.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
They are numbers because they are a result of a calculation formula which I have set as Double data type.
The issue is with the render treating similar numbers inconsistently when they are output as I explained.
