Missing Negative number when extracting data from text format
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
Solved! Go to Solution.
- Labels:
- Common Use Cases
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks for helping out @Emil_Kos 👍
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @AngelosPachis ,
I have run the result and not sure why my result shown as below :
Before
After
Formula used in my workflow:
