Detection of Positive and Negative Amount

[SH_94]

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.

[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

[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.

[AngelosPachis · Accepted solution]

@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

[SH_94]

Hi @AngelosPachis ,

Thanks a lot for the detail explanation. It is clear and easy to understand.

I'm able to run the figures successfully.

Thank you.

[SH_94]

Hi @AngelosPachis ,

I had tried another set of data and would like to clarify with you on the following items. For this formula tool, is it we can only use it when it is in the text format as per first screenshot below? I had tried to use in for the numeric column and it appear the error as per second screenshot.

[AngelosPachis]

Yes, so the ReplaceChar formula can only be applied on string data columns @SH_94 , as the error tells you.

What made you apply it into numeric fields? Those fields won't have any brackets if they contain negative values, and you won't have commas in them.

[SH_94]

Hi @AngelosPachis ,

Reason being i try to apply is i have the yellow/orange colour in each header as per screenshot below and i just worried that it will cause Alteryx unable to read it correctly. May i know what it mean if we have this colour in the header column in the Alteryx? 

[AngelosPachis]

@SH_94  yes of course,

So Alteryx uses yellow colour in column headers to indicate that there are some null values contained in that particular field. It's a way for Alteryx to give you a heads up for the values contained in that column.

As you can see, you have some yellow, then a bit of green and then it stops; if you want to get a full bar appearing across a header, then stick a browse tool in your workflow and while having the browse tool selected you will be able to see a complete data profiling of those columns.

If you hover your mouse over it, it will give you a message like X% nulls, Y% ok and so on. The different colours you can have are those below:

Green : OK

Red : NOT OK

Yellow : NULL

Grey : EMPTY

Null cells are those that don't have any values, but are slightly different from empty values. You can remove them by using a data cleansing tool and replace them with blanks(empty) but that wouldn't make any difference. Probably they won't be a problem in your workflow, but ideally you would like to remove them (by filtering them out for example)

The formula I provided above has nothing to do with those nulls and will not remove them if that's what you are after