Assign Rank

[jaiiracha]

Hello - I'm trying to assign a rank to the below sample dataset. What I would like to accomplish here is adding a single rank value to every row that starts with "312" in COL_1 and assign that same rank value to the rows that follow until we hit another "312" in COL_1. Then the rank value should change to the next value in the rank i.e "2". I've also attached what the desired output should looks like for reference. 

COL_1	COL_2
312	901029
AB	asdd
312	4901233
AB	fasd
312	120921
AB	tro
BC	dfds
AB	def
312	4214111
BC	ar
EF	dms

Desired Output:

COL_1	COL_2	Rank
312	901029	1
AB	asdd	1
312	4901233	2
AB	fasd	2
312	120921	4
AB	tro	4
BC	dfds	4
AB	def	4
312	4214111	5
BC	ar	5
EF	dms	5

Thank you!

[ShankerV]

Hi @jaiiracha 

Yes, it is possible with the help of Multi row formula tool.

Shanker V

[IraWatt · Accepted solution]

Hey @jaiiracha,

I think what you are looking for is the multi row formula to check the results on multiple rows:

This formula checks if the value is 312 if it is then add one to the rank row above.

If you want to learn more about the Multi-Row-Formula Tool the community has some quick and easy videos on the topic here: https://community.alteryx.com/t5/Interactive-Lessons/Multi-Row-Formula/ta-p/82872

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com 

[Felipe_Ribeir0 · Accepted solution]

Hi @jaiiracha 

One way of doing this

[ShankerV · Accepted solution]

Hi @jaiiracha 

Solution from my end

Shanker V

[ShankerV]

@jaiiracha 

Breaking my solution for your convenience.

The multi row formula tool, helps to create a new row and input the values in each row based on the condition satisfied.

if ([COL_1]="312")
then [Row-1:Rank]+1
else [Row-1:Rank]
endif

If the condition is met, then it increases the rank + 1

else it inputs the same previous rank.

This way it helps to reach your expected output.

Kindly accept this solution if it provided a solution to your question.

Many thanks

Shanker V

[jaiiracha]

Thank you all! All great solutions and get the job done!

[IraWatt]

Great to hear @jaiiracha have a good one !