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

If statement to select a range

julianhoetzel
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
messi007
15 - Aurora
15 - Aurora

@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
8 - Asteroid

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
15 - Aurora
15 - Aurora

@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
8 - Asteroid

Sure, this is a similar sample. 

messi007
15 - Aurora
15 - Aurora

@julianhoetzel,

 

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

messi007_0-1608566519644.png

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
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?

messi007
15 - Aurora
15 - Aurora

@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

messi007_0-1608567765374.png

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,

Labels