If statement to select a range

[julianhoetzel]

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 🙂

[messi007]

@julianhoetzel,

Please see the formula below:

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

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

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

Regards,

[julianhoetzel]

Hi, 

thank you for your reply. 

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?

[messi007]

@julianhoetzel,

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,

[julianhoetzel]

Sure, this is a similar sample. 

[messi007]

@julianhoetzel,

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!

[julianhoetzel]

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

[messi007 · Accepted solution]

@julianhoetzel,

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.

Please see below

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,

If this solves your issue please mark the answer as correct, it will help others!

Regards,