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.

[Luke_C · Accepted solution]

@SH_94 

Based on the error message you are trying to apply a math function to a string. Can you check if Days or Invoice Amount (Ori) are strings? Once you update them to be numeric using a select tool it should work.

[RolandSchubert · Accepted solution]

Hi @SH_94 ,

it seems, the field [Invoice Amount (Ori)] is a string field. Replace [Invoice Amount (Ori)]  by ToNumber([Invoice Amount (Ori)]) , thus you convert the string to a number and it should work.

Let me know if it works for you.

Best,

Roland

[SH_94]

Hi @ Lukecornetta and Aurora RolandSchubert,

Thanks a lot for your great information

[SH_94]

Hi RolandSchubert,

It appear the error as below after i put the formula : ToNumber. Can you please advise on this?

[RolandSchubert]

Hi @SH_94 ,

you should use a new field (e.g. [Invoice Amount] and set the data type to double, currently you try to convert a string to a number, but the result is still a string field.  The formula should be ToNumber([Invoice Amount (Ori)], the error is caused by the "'" (first character in formula)

Hope this is helpful.

Best,

Roland

[SH_94]

Hi @ RolandSchubert,

If i change to double, it will show the following error. For instance, the value become 15 instead of 15171.32. May i know how to fix this error?

[RolandSchubert]

That's strange. The decimal separator seems to be a ".", so the thousands separator is assumed to be a ",".

You could try ToNumber([Invoice Amount (Ori)], 0, 0, '.') 

[SH_94]

Hi @RolandSchubert,

Below are the two screenshot on the invoice amount setting.

However, it still give me 15 instead of 15,157.32.

May i know if the above setting is correct?

[RolandSchubert]

Okay, now I see the problem ... conversion by simply changing data type does not always do the job.

But - to solve your problem, only a small modification is needed.

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, '.')

The new field has the correct name and data type, it should show the right amount now. I'm not sure, if you need the field [Invoice Amount] for the connection to the RecordId tool, maybe you have to connect this to the formula tool.