Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- JMP User Community
- :
- Discussions
- :
- Rank Unique

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Aug 19, 2020 1:34 AM
(336 views)

I want to calculate a new column to rank a column uniquely. Below are the example output that i want to achieve. But by using the JSL rank formula doesn't help me to achieve what i want. Can anyone please advise me?

The formula i use is Col Rank(:Label)

Label | Rank | What I want |

NVM_VMAX_PRG_EVENR2_DCMP[1],80376,max | 1 | 1 |

NVM_VMAX_PRG_EVENR2_DCMP[1],80376,max | 2 | 1 |

NVM_VMAX_PRG_EVENR2_DCMP[1],80376,max | 3 | 1 |

NVM_VMAX_PRG_EVENR2_DCMP[1],81999,max | 4 | 2 |

NVM_VMAX_PRG_EVENR2_DCMP[1],81999,max | 5 | 2 |

NVM_VMAX_PRG_EVENR2_DCMP[1],81999,max | 6 | 2 |

NVM_VMAX_PRG_EVENR2_DCMP[1],86491,max | 7 | 3 |

NVM_VMAX_PRG_EVENR2_DCMP[1],86491,max | 8 | 3 |

NVM_VMAX_PRG_EVENR2_DCMP[1],86491,max | 9 | 3 |

2 ACCEPTED SOLUTIONS

Accepted Solutions

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

There are a couple of ways to do this....(that I can think of, this early in the morning)

- Use a summary table to create the ranks
- Run Tables==>Summary
- Group by your Label column
- Assuming the output data table is sorted from low to high, create a new column called "Rank" and give it the formula........Row()
- Run Tables==>Update to join the summary table back to the original table, matching on the Label column

- Create a new Rank formula column
- Sort the data by Label
- Create a new column called Rank with the formula
`If( Row() == 1, count = 1, If( :Label != Lag( :Label ), count ++) ); count;`

- Be sure to go to Column Info and remove the after the column is created. The reason for this is that since the formula is based upon the order of the data, and the completeness of the data, any changes in the data(sorting etc.) will rerun the formula and possibly change the rankings.

Jim

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

If you want a column formula this should work. Independent of sorting.

`Local( {L = Associative Array( :Label ) << get keys}, Loc( L, :Label ) )`

Same approach could be used in JSL without a column formula.

```
dt = Current Data Table();
dt << New Column( "Rank Unique", continuous );
L = Associative Array( :Label ) << get keys;
For Each Row( :Rank Unique = Loc( L, :Label ) );
```

5 REPLIES 5

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

There are a couple of ways to do this....(that I can think of, this early in the morning)

- Use a summary table to create the ranks
- Run Tables==>Summary
- Group by your Label column
- Assuming the output data table is sorted from low to high, create a new column called "Rank" and give it the formula........Row()
- Run Tables==>Update to join the summary table back to the original table, matching on the Label column

- Create a new Rank formula column
- Sort the data by Label
- Create a new column called Rank with the formula
`If( Row() == 1, count = 1, If( :Label != Lag( :Label ), count ++) ); count;`

- Be sure to go to Column Info and remove the after the column is created. The reason for this is that since the formula is based upon the order of the data, and the completeness of the data, any changes in the data(sorting etc.) will rerun the formula and possibly change the rankings.

Jim

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Rank Unique

Hi Jim,

Thanks for advice. For the #2 solution. How do remove the formula by using JSL instead of doing it manually?

Thanks for advice. For the #2 solution. How do remove the formula by using JSL instead of doing it manually?

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Rank Unique

```
dt = current data table();
dt:Rank << delete formula;
```

Jim

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

If you want a column formula this should work. Independent of sorting.

`Local( {L = Associative Array( :Label ) << get keys}, Loc( L, :Label ) )`

Same approach could be used in JSL without a column formula.

```
dt = Current Data Table();
dt << New Column( "Rank Unique", continuous );
L = Associative Array( :Label ) << get keys;
For Each Row( :Rank Unique = Loc( L, :Label ) );
```

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Rank Unique

Thanks Jim & MS. It work brilliantly!