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

- JMP User Community
- :
- Discussions
- :
- ColumnRank by group?

- 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

Jun 14, 2016 8:27 AM
(5833 views)

Hello,

Is there a way to build a formula equivalent to «ColumnRank by…» (group). I normally use the distribution platform (with «by group» option) and save ranks for that purpose in a static column. But this time, I need a dynamic column (calculated).

Thanks,

Claude

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

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

Hi all,

The Col Rank function does now support BY variables as was originally requested. It looks like full support was added in the JMP 13 timeframe, so if you're using that version or a more recent one, you should be able to do this.

The online JMP Help gives a few more details and tie-break options:

I know this is an older thread, but wanted this information available for anyone else who may be looking for it.

Cheers!

Audrey

11 REPLIES 11

Highlighted
##

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

Re: ColumnRank by group?

Hi Claude,

The Col Rank(column, ) function does what you’re looking for. The default for breaking ties is row order, but there’s an optional third argument that allows for some other choices. Hope this helps.

-Jerry

Highlighted
##

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

Re: ColumnRank by group?

Hi Jerry,

Can you explain more please. The manual is very minimalist about this third argument.

Col Rank

Ranks each row’s value, from 1 for the lowest value to the number of non‐missing columns for the highest value. **Ties are broken arbitrarily**.

I don’t understand what it might mean and how to use it.

Thank you for your help!

Claude

Highlighted
##

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

Re: ColumnRank by group?

I'm afraid the optional arguments for Col Rank() is not supported by the current JMP version.

I do not have a good workaround that would work truly dynamically. The column formula in the example below should work but only if the variable is sorted within groups.

dt = Open**(**"$SAMPLE_DATA/Big Class.jmp"**)**;

dt << **sort****(**by**(**:age, weight**)**, Replace table**(****1****))**;

F = Expr**(**

Eval**(**Eval Expr**(**Col Number**(**:weight, Row**()** <= Expr**(**Row**())**,:age**)))**

**)**;

dt << **New Column****(**"Rank of weight by age", numeric, formula**(**Name Expr**(**F**)))**;

Highlighted
##

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

Re: ColumnRank by group?

Hi MS,

Well, this is a very interesting workaround. It’s open to me a completely new area (Eval, Formula, etc.).

I'm crossing my fingers for a future JMP version with this new function "ColRank by…".

Thanks a lot for taking time to answer my question.

Claude

Highlighted
##

Sorry for the confusion, I apparently wasn’t paying attention to which version was active on my system when I was looking at this. So, as a result, you’ve gotten an “unauthorized” preview of functionality planned for a “future” JMP version.

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

Re: ColumnRank by group?

Highlighted
##

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

Re: ColumnRank by group?

Hi Jerry,

No problem. I'm happy to see that one day my wishes will come true!

Thanks a lot again.

Claude

Highlighted
##

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

Re: ColumnRank by group?

I am also dissatisfied with the idea of "**Ties are broken arbitrarily"**. there should at least be an option to change that to ties get same rank like would happen in a regular ordinal variable.

I have another way to "rank by" without sorting the table - in my case sorting is a nuisance. it gives equal values to the ties.

best,

ron

Names Default To Here**(** **1** **)**;

// define the data source

dt = Open**(** "$SAMPLE_DATA/Big Class.jmp" **)**;

// add a column for the rank

dt << **New Column(** "Rank" , numeric, ordinal**)**;

//// option 1: Rank by Column

// extract the list of the by variable levels

byvarlist = associative array **(**dt:age**)** << **get keys**;

// create seperate rank lists of height for each age group

for **(**ib = **1**, ib<= nitems**(**byvarlist**)**, ib++,

eval **(**parse**(** "heightlist"||char**(**byvarlist**[**ib**])**||" = Associative Array( "|| char**(** dt:height**[**dt << **get rows where** **(**:age == byvarlist**[**ib**])])**||" ) << get keys"**))**;

**)**;

for **(**ic = **1**, ic<=nrows**(**dt**)**, ic++,

:Rank **[**ic**]** = eval**(** parse **(** "loc( heightlist "||char**(**dt:age**[**ic**]** **)**||" ,"||char**(** dt:height**[**ic**])**||" ) " **))**;

**)**;

//see if it worked

dt<< **Sort(**

By**(** :age, :height **)**,

Order**(** Ascending, Ascending **)**

**)**;

////

//// Option 2: Rank (without a by column)

// get the unique values for height as a list

heightlist = Associative Array**(** dt:height **)** << **get keys**;

For**(** i = **1**, i <= N Rows**(** dt **)**, i++,

dt:Rank**[**i**]** = Loc**(** heightlist, :height**[**i**]** **)**;

**)**;

//see if it worked

dt<< **Sort(**

By**(** :height **)**,

Order**(** Ascending **)**

**)**;

Highlighted
##

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

Re: ColumnRank by group?

Thanks a lot Ron. It is an ingenious way to solve the problem.

Unfortunately, I cannot use script because I have to go through a computed column. In fact, if I add columns with hard data, the update to my table with my database (via DNS/ODBC) will create a new table instead of simply replace data.

Claude

Highlighted
##

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

Re: ColumnRank by group?

hi claude.bellavance,

if there is an issue with an update, all you need to do is delete the hard coded columns just before the update and re-introduce them immediately after.

such as:

delete hard coded columns;

update;

re-calculate the hard coded columns;

best,

ron

Article Labels

There are no labels assigned to this post.