This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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.
Hi @ahsankhalid
Here's one way to do it
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.
Dan
Use the Tile tool.
Method: Unique values.
Unique Fields: Column E
Grouping Fields: Column A
Output: TileNumber gives you the Rank2 output.
Sample input:
Sample output:
Tool configuration:
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).
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.
Hi @ahsankhalid
It looks like I misread what you miswrote, since my solution gives the answer in the picture.
Dan
The Tile tool unfortunately doesn't have the option to change the direction of the sequence, although you can get there using this workflow:
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
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" checked
Workflow Overview