IF Error in the Alteryx

[SH_94]

Hi Community ,

I would like to seek your help to comment on the following screenshot as i not sure why the formula is not working in this case.

Could anyone please advise me on the mistake that i have made?

Thanks a lot.

[SH_94]

Hi @ RolandSchubert,

It seems to be same result. Kindly refer to the screenshot below.

Do we need to fix the invoice amount first ?

[RolandSchubert · Accepted solution]

You could remove the comma in a first step using the formula Replace([F4], ',', '') for field [F4] without any changes on data type and use ToNumber([F4]) in a second step. What's the result? 

[SH_94]

Hi @ RolandSchubert,

Amazing, I managed to get the results that i wanted. Thanks a lot for your help.

But i still don't understand on the concept below whereby we keep the name as F4 and in the formula tool add it new column. Could you briefly explain the concept/logic behind why we need to do in this way :

In the Select tool, do not change the data type and field name (so the name is still [F4] and it's a V_WString).

In the Formula tool, add a new column and name it Invoice Amount (Ori)", set the data type to double and use the formula ToNumber([F4], 0, 0, '.').

Thanks again for your help.

[SH_94]

Hi @ RolandSchubert,

There are some errors as shown in the screenshot below. Do you have any idea why it has the error?

[RolandSchubert · Accepted solution]

Hi @SH_94 ,

I think, the brackets indicate negative numbers. You could add two additional Replace functions (two additional formulas, both  with [F4] selected

Replace([F4], '(', '-')      to replace the opening bracket by a minus sign
Replace([F4], ')', '')        to remove the closing bracket

This should convert (1764.23) by -1764.23, and this will be converted to a number correctly.

Give it a try.

Best,

Roland

[SH_94]

Hi @ RolandSchubert,

Thanks a lot again and it works well.

Sorry for asking so many question as I quite new to this Alteryx and really appreciate your guidance and solutions.

Thank you for your help