Detection of Positive and Negative Amount

Hi Community,

Currently i had negative and positive figures in two different set : Data A and Data B as per screenshot below. May i know is it possible for alteryx to auto detect whether it is positive or negative figures?

It is because i plan to use the if formula as below in Alteryx. Not sure if this is doable in Alteryx.

1. If both Data A and B are positive figures, then Data A minus Data B

2. If Data A is negative and Data B is positive figure, then Data A plus Data B

3. If Data A is positive and Data B is negative figure, then Data A plus Data B

4. If both Data A and B are negative figures, then Data A minus Data B

Thank you so much for your help.

Positive and Negative Amount.xlsx

Common Use Cases

Accepted answers

AngelosPachis

@SH_94 one way to do the data cleansing is with a multi-field formula tool.

That tool will allow you to apply a certain formula across the selected fields, but also change the data type to those fields. I have tried to explain the logic behind the expression I used with comments in the tool itself.

What it will do is check if a cell contains a "(" since that's an indication that this is a negative value. If it does, it will replace all instances of brackets or commas with nothing (so essentially it will remove them). If it doesn't include a "(" then it's a positive value, so you will only remove the commas.

Angelos

example_v2.yxmd

All comments

AngelosPachis

Hi @SH_94 ,

You can do that with an If statement inside a formula tool. Here are two expressions that you can use to get the desired outcome

Hope that helps,

Angelos

example.yxmd

philipmannering

There's no sign() function in Alteryx unfortunately, but here's one way you could get the right result: Use a formula tool with expression,

IF [Data A] * [Data B] < 0 
THEN [Data A] + [Data B]
ELSE [Data A] - [Data B]
ENDIF

Thanks,

Philip

SH_94

Hi @AngelosPachis ,

Thanks a lot for the prompt response.

I had tried the formula and it works perfectly. However, sometimes it is unable to detect as Alteryx detect it as ( ) instead of negative sign. I plan to do the overall data cleansing on the value itself. May i know normally how you will clean the figures so that alteryx will read it successfully for those figures with these two symbol ( ) and , .

May i know if there is anyway to clean the figures thoroughly, no matter what it is the format or symbol so that we can apply the formula successfully?

Thank you so much again for the help.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703