Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

IF Error in the Alteryx

SH_94
9 - Comet

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.

Jacob_94_0-1615305568917.png

 

15 REPLIES 15
SH_94
9 - Comet

Hi @ RolandSchubert,

 

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

 

Do we need to fix the invoice amount first ?

 

Jacob_94_0-1615309643692.png

 

RolandSchubert
15 - Aurora
15 - Aurora

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
9 - Comet

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
9 - Comet

Hi @ RolandSchubert,

 

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

Jacob_94_0-1615313368150.png

 

RolandSchubert
15 - Aurora
15 - Aurora

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
9 - Comet

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

Labels