Conversion Error

[MM29]

I am new to Alteryx so this might be a lame query. My data field (no. of pieces per pack) has numeric values and some text values such as 'Not Defined' / Not Available' / Unidentified'. I used Select Tool to convert the field from V_String to Double. Then when i use it in Formula to calculate  Price/ Piece, it shows Conversion Error as 'Not defined/ Not Applicable.... not a valid number. Do I convert all Non-numeric data into Null? Is that the right way approach? Thanks.

[paulfound]

Hi @MM29,

Welcome to the world of Alteryx! 🙂

I think from the sounds of the question, your problem is with the formula tool, if you create a new field, the data type will need to be double, not string for example.

[MM29]

Hi Paul,

Thanks for your prompt response. I am not able to solve it yet. Let me share more details. 

Alteryx defines the field Pack Sizes (Pieces) as V_String. It has number vales (1 to 100) and a few records for which this field is labelled ' Not defined' or 'Not Applicable' or Not Available'. When I use this field to calculate Price/ Piece, the Formula Tools gives an error if I do not change data type for 'Pack Sizes (pieces)' from V_String to Double. So using Select Tool (as in the workflow below), I changed the data type to double.

And the data type re-defined below using Select Tool (highlighted above)

But it still gives conversion error (as below). 

 

Hope it explains better. 

Thanks in Advance. 

[afv2688]

Hello @MM29 ,

First you need to know what value you wanna give to those cells which have the labels ' Not defined' or 'Not Applicable' or Not Available'.

My recommendation would be to give them some absurd value which helps you identify them quickly, on your example it could be something like 999.

Therefore what I would write in a new cell with the formula tool would be something like this

IF [PIECES] = ' Not defined' OR [PIECES] = 'Not Applicable' OR [PIECES] = 'Not Available' THEN 999 ELSE ToNumber([PIECES]) ENDIF

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regards

[paulfound · Accepted solution]

I agree with @afv2688 you could add a number, but that would end up in your calculations further down field, so either make it 0 value so this doesn't inflate an sums or maybe it would be best to filter the text values out and then union back in at a later stage. 

Hope this helps

[MM29]

Thanks Paul and @afv2688. Would you recommend making it 0 or null()? What could be possible implications of making it null()? 

[afv2688 · Accepted solution]

If you make the value null it's going to appear empty in the end. Depending on your calculations.

If you let the value be 0, even when your range varies between 1 and 100 some user may think it's the actual result you got.

If for you or your users its ok I would better recommend to use null().

Regards