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!

[cplewis90]

Hello @dishamishra,

Can you share a sample of the Excel file with the formatting to use in a workflow? I think I have an idea on how to solve, but want to test before suggesting it.

[TheOC · Accepted solution]

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!

[dishamishra]

Hi there @cplewis90 ! For sure - please see the attached file 🙂 

[TheOC]

My Mistake! I think i see what you mean now, i'll have another look!

[dishamishra]

Hi @TheOC ! 

I tried out your solution and it seems that this has worked! Thank you!!

Feel free to take a look at that other attachment I shared, but I think your solution was really good and what I was looking for! 😄 

[TheOC]

Hey @dishamishra 

oh thats fantastic! Yeah i did the same, but i had saved your file after opening it in excel as a CSV, and then the formula worked - i'd recommend running a multi-field formula over all the fields you want to run this over! 

Glad i could be of assistance, have a great weekend!

[dishamishra]

@TheOC Thank you once again, and you have a great weekend as well!