Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

IF Error in the Alteryx

SH_94
11 - Bolide

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
11 - Bolide

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
16 - Nebula
16 - Nebula

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
11 - Bolide

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
11 - Bolide

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
16 - Nebula
16 - Nebula

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
11 - Bolide

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