Negative Custom Formats from Source Files Returning Nulls when Converted to Double

dishamishra

HI there,

I'm hoping to get some insights from the team of Alteryx experts out there!

I have an excel file which has a custom format being applied to negative numbers. Please see the below screenshot:

When I load the data into Alteryx, all numbers are loaded as "V_WString" data types. Now, when I use a Select tool to convert the numbers to "Double" data type, all these negative numbers return NULL values in my output, whereas the positive numbers are fine. 

I've been searching and trying some options around the forums for many hours with no luck. Does anyone have an idea how I can correct this?

I have multiple columns of such numbers, by financial quarter (i.e. Q1 2019, Q2 2019, etc.)

Many thanks!

TheOC

Hi @dishamishra !

These fail because the whole of the field is not a number ,and so when it tries to convert it fails and reverts to null. A solution for this is to check for the brackets, and then replace them with a - at the start, this can then be converted to your double!

Please check this solution provided. I have put on a select to show that it can be changed, but you can change it in the formula tool instead for more efficiency!


Cheers!

minusnumbers.yxmd