6/17/21: We have completed maintenance for the Search functionality on the Community. If you are seeing any issues, please try to clear your cache first. If the issue persists please email Community@alteryx.com
alteryx Community

Alteryx Designer Discussions

SOLVED

If statement to select a range

8 - Asteroid

Hi everyone,

I am pretty new to Alteryx. So I hope someone can me out 🙂

How can I write an IF Statement with the following conditions:

I need to determine the scores that match with "Share of total payables aging".

For instance, "Sum not due" which is 1.41 is supposed to have a score of 600 since it is way above the 0.125 threshold whereas "Sum_due within 30 days" (-0.359) is supposed to have a score of 100 since it is below 0.00.

Can someone help me? Thank you 🙂

7 REPLIES 7
13 - Pulsar

``````if [name]="Sum not due" and [Share of payable aging]>0.125 then "600"
elseif [name]="Sum_due within 30 days" <0 then "100"
endif``````

if you want to have the result as Double or Integer please delete the brackets on the result.

Regards,

8 - Asteroid

Hi,

My main problem is that I do not want to write out the scores in the formula as "600" or "100". Rather I want kind of a XLOOKUP that if a certain condition is fulfilled, refer to score XY.

I might change the scores within time since they will be connected to many other determinants.

Does that make sense or is it feasible to calculate such a case?

13 - Pulsar

Yes you can check against the column Range 2 in your case and then get the score value.

Could you share a sample data of the input instead of .png.

It will help to make the formula for you.

Best Regards,

8 - Asteroid

Sure, this is a similar sample.

13 - Pulsar

I used a group by with Min max in order to get only one value and check aginst it:

If you want to do more comparison we can use filters as well in order to apply condition.

Attached the workflow,

Hope that helps!

8 - Asteroid

Thanks. Yes, it does. What if a value is, lets say 0.06. The score should be then 300. How would you calculate that?

13 - Pulsar

I updated the input with 0.06,

Correct me if I'm wrong. If it's 0.06 it have to be 400 as it's between 0.05 and 0.075.

I'll let you update the formula below:

``````if IsNull([Share of payables aging])
then 0
elseif [Share of payables aging]<=0
then [1] //it's the first scroe
elseif [Share of payables aging]>0 and [Share of payables aging]<=0.025
then [2]
elseif [Share of payables aging]>0.025 and [Share of payables aging]<=0.05
then [3]
elseif [Share of payables aging]>0.05 and [Share of payables aging]<=0.075
then [4]
elseif [Share of payables aging]>0.075 and [Share of payables aging]<=0.1
then [5]
else [6]
endif``````

Attached the workflow,