Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Alteryx is here to help you solve your biggest data challenges. Read about the new Virtual Solution Center here.

rank the values in a column from highest to lowest

Highlighted
8 - Asteroid

Hi Guys, 

 

Here is my dataset and I am trying to create a new Column "Rank 2". This colum will analyze the values in Column E(Rank) for each Framework(Col A). 

 

The lowest value gets 1 and highest gets 4 in this case but could be as much as 60. 

 

Please can someone show me how I can get the Rank 2 Column. 

 

 

ahsankhalid_0-1581679743897.png

 

Highlighted
17 - Castor
17 - Castor

Hi @ahsankhalid 

 

Here's one way to do it

 

w.png

 

After removing the original Rank 2 Column, add a RecordID to return your records back to the original order.  Sort by Framework ASC and Rank Desc.  This puts the highest ranks as the first record for each framework and they decrease after that.  Use a Multi-Row tool that increments previous Rank 2 value by one.  Sort by RecordID to get your original order.

 

r.png

 

Dan

Highlighted
9 - Comet

Use the Tile tool.

Method: Unique values.

Unique Fields: Column E

Grouping Fields: Column A

Output: TileNumber gives you the Rank2 output.

 

Sample input:

tonyp_0-1581682804579.png

Sample output:

tonyp_1-1581682839356.png

 

Tool configuration:

 

tonyp_0-1581683049819.png

 

Highlighted
8 - Asteroid

Hi tonyp, 

 

The output is in ascending order where it give rank 1 to the lowets value. Anyway I can get 1 to the highest value(which is always 1). 

Highlighted
8 - Asteroid

Sorry guys I think I have written the opposite of what I wanted. I want the highest value(that is always 1) to be ranked 1st and then do descending. 

Highlighted
17 - Castor
17 - Castor

Hi @ahsankhalid 

 

It looks like I misread what you miswrote, since my solution gives the answer in the picture.

 

Dan

Highlighted
9 - Comet

The Tile tool unfortunately doesn't have the option to change the direction of the sequence, although you can get there using this workflow:

tonyp_0-1581686691226.png

Summarise contains Group By and Count of your group column (A).

Join brings the summarised count into your main data.

Formula then reverses the tile order using Tile-num = Count - Tile_num +1

 

Highlighted
12 - Quasar

While the tile tool does not have an option to change the rank order as @tonyp mentions...you could get the same results by sorting the data before using the tile tool as below.

 

 

Tile Settings - make sure you have "leave unsorted" checkedTile Settings - make sure you have "leave unsorted" checkedWorkflow OverviewWorkflow Overview

Labels