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

Formula Tool - Issue

JDong
8 - Asteroid

Hi Team,

 

I get the below error with this IF statement...how to overcome this issue

 

Gallop_0-1589187727875.png

 

IF Status = 'Yes' and [percent] = '100.00%' THEN '0.00%'
ELSEIf Status = 'No' THEN [percent] / [Total_percent]
else [percent]
ENDIF

 

 

Thanks

9 REPLIES 9
Jonathan-Sherman
15 - Aurora
15 - Aurora

Hi @JDong,

 

My guess is your issue is with the "THEN  [percent] / [Total_percent]" part of your formula as these fields are likely strings looking at the error message.

 

Perhaps change your formula to

 

IF Status = 'Yes' and [percent] = '100.00%' THEN '0.00%'
ELSEIf Status = 'No' THEN ToString((ToNumber([percent]) / ToNumber([Total_percent])*100), 2) + '%'
else [percent]
ENDIF

 

If this solves your issue please mark the answer as correct, if not let me know!

 

Regards,

Jonathan

vishnuharinarayanan
7 - Meteor

Hi ,

 

Can you check the  data type of the column where you are applying the numeric operator, as the error message says that Numeric Operator is applied to a String Value?

 

The column Percent seems to be of String Data Type.

 

Numeric operators can be applied only to columns in the Number format ( either be Double, fixed decimal etc.)

 

Please let me know if the issue is resolved.

 

 

RolandSchubert
16 - Nebula
16 - Nebula

Hi @JDong ,

 

I think, the error is caused by the [percent] column. '100.00%' seems to be a string data type, in your formula you try to calculate using this string.

A few changes should solve the problem - try:

 

IF Status = 'Yes' AND [percent] = '100.00%' THEN
'0.00%'
ELSEIf Status = 'No' THEN
ToNumber(Replace([percent], '%', '')) / [Total_percent]
ELSE
ToNumber(Replace([percent], '%', ''))
ENDIF

 

If [Total_percent] is also string, you should use ToNumber as well.

 

Best,

 

Roland

JDong
8 - Asteroid

Hi @Jonathan-Sherman 

 

It says unknown variable ToNumber

 

The percent field is a fixed decimal. Thanks

JDong
8 - Asteroid

@RolandSchubert 

 

When I do this get an error

 

Gallop_0-1589191222253.png

 

Thanks

 

Jonathan-Sherman
15 - Aurora
15 - Aurora

Hi @JDong,

 

Could you share a sample of the data? Or a mock up - with your formula?

 

Regards,

Jonathan 

JDong
8 - Asteroid

IF Status = 'No' and [percent] = '100.00%' THEN '0.00%'
ELSEIf Status = 'Yes' THEN ToNumber(Replace([percent], '%', '')) / ToNumber(Replace([Total_percent], '%', ''))
else ToNumber(Replace([percent], '%', ''))
ENDIF

Jonathan-Sherman
15 - Aurora
15 - Aurora

Where are your Status and Total_percent fields? And is your formula updating a field or creating a new column?

JDong
8 - Asteroid

Thanks Jonathan...the above calculation works !

 

I had to change the datatype of percent and total percent fields.

 

 

Labels