community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

Formula Error

Meteor

 

 

 

Hey!

 

I have a formula that doesn't seem to be functioning properly.

 

The formula should only be picking up KDE that = Probability of Default" with specific Merival + Zeus values but it is putting a yes for all KDEs.

 

Anyone have any idea?

 

PD Floor.PNG

Quasar
Quasar

What are KDE and Merival?  If they are data points from earlier in the workflow, then they need to be in brackets.  Also, are the pluses supposed to be "and" ?  If so, change it to "and".  The plus is a concatenator for strings (or sum for numbers).

Alteryx Certified Partner

I believe you are mixing text data with numbers I think will not work. Validate the numbers first with 'yes' or 'no' and then make the formula using the 'KDE

Alteryx Certified Partner
Alteryx Certified Partner

With 2 posts for the same question, there's lots of potential solutions for you.  As suggested, you should check the variable data type so that you use appropriate values in your formula.

 

"Mark" < 100 is False.  Not because my age is really greater than 100, but because numbers sort before letters when comparing them alphabetically.  When putting an argument in quotes, you're looking at a string.  Alteryx uses the convention of placing field names inside of square brackets.  You MUST use the convention if the field name has a space inside of it or if the field name is entirely numeric.

 

What is a number?  If you can perform math on the value, I'd store it as a number.  If the data is entirely numeric (e.g. Phone, ZIP, Grade in School) it isn't necessary to store it with a numeric data type.  Sure it saves space, but it really isn't a number.  For your formula, you can temporarily convert a value (e.g.  0.0003) from a string to a number by use of the ToNumber() function.  But as a practice, I'd use a select and change the data type instead.

 

I hope that you have solved the challenge.  I'd also suggest using the interactive training as (from my memory) it covers some data type lessons.

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Quasar

Hello @emiafshar ,

 

Building on what @MarqueeCrew mentioned about the data types, you could use either a select tool or a multi-field formula tool to convert the data types of the Zeus and Merival fields into numbers. 

Then remove the quotes from the numbers in your IF statement, and replace the + with the boolean AND.

If you want to leave the fields as string, then just use the tonumber() function around the fields.

So your formula would read something like this :

IF [KDE]="Probability of Default" AND [Merival]=0.0003 AND [Zeus]<0.0003 THEN "Yes" ELSE "No" ENDIF


Or, if you don't convert [Merival] and [Zeus]:

IF [KDE]="Probability of Default" AND tonumber([Merival])=.0003 AND tonumber([Zeus])<.0003 THEN "Yes" ELSE "No" ENDIF

Cheers!

Esther

Labels