Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

dishamishra
7 - Meteor

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:Numbers.jpg

 

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!

7 REPLIES 7
cplewis90
13 - Pulsar
13 - Pulsar

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
15 - Aurora
15 - Aurora

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!


Bulien
dishamishra
7 - Meteor

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

TheOC
15 - Aurora
15 - Aurora

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


Bulien
dishamishra
7 - Meteor

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
15 - Aurora
15 - Aurora

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!


Bulien
dishamishra
7 - Meteor

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

Labels