Missing Negative number when extracting data from text format

Hi @SH_94 ,

Alteryx doesn't recognise negative values as those within brackets, but those with a minus sign at the front. Hence, it treats as columns with values within brackets as string data type, and when converting to a double you are losing the actual values because it's like trying to convert your name into a number.

To change that, you can apply a formula into multiple fields (hence a multi-field formula) that checks if a cell contains a bracket; that would mean that the value is negative. Then if it does, you can replace those brackets with nothing (so remove them essentially) and to that particular cell you can add a minus sign at the front

Fun fact is that you can change the field type without using a Select tool, from within the Multi-field formula tool

Screenshot 2021-04-04 184257.jpg

Hope that helps,

Angelos

example.yxmd

All comments

Hi @SH_94 ,

Fun fact is that you can change the field type without using a Select tool, from within the Multi-field formula tool

Screenshot 2021-04-04 184257.jpg

Hope that helps,

Angelos

example.yxmd

Thanks a lot for the input. I had tried this formula before and it give me the result as below . It is because i need to choose the numeric field as per screenshot below so that it will present all the numeric field. May i know how to solve this issue in this case?

Hi @AngelosPachis and @Emil_Kos ,

Thank you so much for the input. It is working now in the workflow.

Appreciate it a lot.😀

Thank you.

Currently i am running different set of data and noted that the formula used will trim the value.

Formula used - IF Contains([_CurrentField_],"(") THEN
-Tonumber(ReplaceChar([_CurrentField_],"()",""))
ELSE [_CurrentField_]
ENDIF

For instance, if the number is (140,000.00) , the result will be -140. May i know how to write the formula so the result will be as follow:

1. Original data : (140,000.00) , Output result : -140,000.00

2. Original data : (140000.00) , Output result : -140000.00

Many thanks again for your help.

Hi @SH_94 ,

You can not have a comma in a numeric field, so if you do need a comma to appear there Alteryx will treat it as a string field. Here are the two ways you can go, the one will give you a string, the other a number:

Let me know if that worked for you please.

Cheers,

Angelos

I have run the result and not sure why my result shown as below :

Before

After

Formula used in my workflow:

apathetichell

@AngelosPachisInterestingly if you have commas in localization under user settings you can keep the ","s... just tested on my system and one of these is a string and one of these is a double...

Screengrab to illustrate...

2021-06-11 (2).png

Interesting @apathetichell ,

Do your user settings look something like this?

EDIT : I tried using your formula with a Tonumber function but left the data type as a string. Oddly, the tool didn't give me any error, but the commas were removed because of that Tonumber function

multi-row formula behaviour.yxmd

@SH_94 can you please confirm that after your "Else" statement, you are using a Tonumber() function please?

It would also help if you send over some mock data based on what you have at your disposal + that multi0field formula tool, so it would be easier to troubleshoot