Missing Negative number when extracting data from text format

[SH_94]

Hi Community, 

Currently i had extracted the data from the txt format. However, i noted that negative amount will be not read in this case. When i click the text to column tool as per screenshot below, it will appear the negative number as per screenshot below.

However, it is surprisingly disappear when i click the select tools as per screenshot below.

From the configuration view of select as per screenshot below , it seems like i was unable to adjust the size of doable and would like to know how to deal with it when we have this issue.

I have also attached the workflow below for your reference.

Thank you so much for the help.

[AngelosPachis · Accepted solution]

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

Hope that helps,

Angelos

[SH_94]

Hi @AngelosPachis ,

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? 

[Emil_Kos]

Hi @SH_94, 

replace char works for the text columns. You need to use @AngelosPachis approach on the text columns. 

when you will change the data type to numeric you will change the negative values to null positions so you need to use @AngelosPachis beforehand.

[SH_94]

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.

[AngelosPachis]

Thanks for helping out  @Emil_Kos  👍

[Emil_Kos]

Hi @AngelosPachis,

It was a pleasure to help someone on the community😅

[SH_94]

Hi @AngelosPachis ,

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.

[AngelosPachis]

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

[SH_94]

Hi @AngelosPachis ,

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

Before 

After 

Formula used in my workflow: