"IF" condition based on multiple criteria - Alteryx

[Anjankumar2021]

Hi, pls assist on the below.

Input:

Table A

Student	Total Marks
Samsung	55
LG	33
Onida	18
Sony	8

Table B

Min	Max	Rank	S.No
0	0	R0	1
1	5	R1	2
6	10	R2	3
11	20	R3	4
21	30	R4	5
31		R5	6

any student with greater than or equal to 31 marks considered as rank R5 .

Required output:

Student	Total Marks	Rank	S.No
Samsung	55	R5	6
LG	33	R5	6
Onida	18	R3	4
Sony	8	R2	3

[Anjankumar2021]

@binuacs pls assist

[alexnajm]

Join on Range might be your best bet! Join on ranges - Alteryx Community

Here's an alternative too: How To: Join on Ranges in SQL and Alteryx

[OTrieger]

@Anjankumar2021  what have you done so far to try to get it solved?

There are several ways how to get it done, some are more bizarre then other.

In additional to the 2 above you as long as you know that table 2 is a fixed rating then you can write it in a Formula tool as a condition for Table 1.

Another way, again just if you are bored, it to append the 2nd table to the first one and then again use the formula tool to create conditions and then those <>= and then filter out those unneeded lines.

One more way is to create a conditions in formula tool in a batch macro and then Control parameter will be to score, get it out from the batch macro and join it to the data prior the batch macro.

There are many ways, how you can get it done, just do it ;) 

[cjaneczko · Accepted solution]

Try the following

if [Total Marks] = 0 then 'R0'
elseif [Total Marks]>= 1 and [Total Marks] <=5 then 'R1'
elseif [Total Marks]>= 6 and [Total Marks] <=10 then 'R2'
elseif [Total Marks]>= 11 and [Total Marks] <=20 then 'R3'
elseif [Total Marks]>= 21 and [Total Marks] <=30 then 'R4'
elseif [Total Marks]>= 31 then 'R5'
else Null()
endif

Then Join the Rank to the Table to get the S.No

[binuacs · Accepted solution]

@Anjankumar2021 another option using generate record tool

[grazitti_sapna · Accepted solution]

Anjankumar2021

On way of doing this by calculating rank in student's table and join both the tables by rank.

Thanks