How to covert a string formula to numerical value
- 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,
I wish to calculate a string type formula to a numerical value. The string data contains variable amount of numbers and plus and minus symbols like '+' and '-'.
I have the following expamle string data:
Field1 | 51.6+31.2-12.1 |
Field2 | 809.50 |
Field3 | 297.1+5+0+123+34.5-22.3 |
Field4 | 5505.00-100-5 |
And I want output as a numerical value:
Field1 | 70.7 |
Field2 | 809.5 |
Field3 | 437.3 |
Field4 | 5400 |
Solved! Go to Solution.
- Labels:
- Data Investigation
- Workflow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @rxing,
Alteryx currently don't have a indirect function as excel has so I've developed a batch macro to help you with that. Take a look
Best,
Fernando Vizcaino
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Many thanks to your help!
By the way, I have some empty input and the marco generate error "Record #9:Tool #4:Parse error at char(0):Empty expression ". The sequence of generated data is also erased. Here is an expamle input:
Field1 | 10+5 |
Field2 | 2.1 |
Field3 | |
Field4 | 3+4.5 |
Field5 | 1+2+3+4 |
And the output is below:
Field1 | 105 |
Field2 | 2.1 |
Field3 | 7.5 |
Field4 | 10 |
The field3 was deleted,however I want to keep this field as empty. Do you know how to solve this problem?
Best,
Ryan Xing
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @rxing ,
It became a bit more complicated but there it is.
If you want to understand a little more about batch macros https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Getting-Started-with-Batch-Macros/t...
Best,
Fernando Vizcaino