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) LabelRankWhat I wantNVM_VMAX_PRG_EVENR2_DCMP[1],80376,max11NVM_VMAX_PRG_EVENR2_DCMP[1],80376,max21NVM_VMAX_PRG_EVENR2_DCMP[1],80376,max31NVM_VMAX_PRG_EVENR2_DCMP[1],81999,max42NVM_VMAX_PRG_EVENR2_DCMP[1],81999,max52NVM_VMAX_PRG_EVENR2_DCMP[1],81999,max62NVM_VMAX_PRG_EVENR2_DCMP[1],86491,max73NVM_VMAX_PRG_EVENR2_DCMP[1],86491,max83NVM_VMAX_PRG_EVENR2_DCMP[1],86491,max93

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 formulaIf( 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.

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Rank Unique

OneNorthJMP

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

Accepted answers

txnelson

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
1. Run Tables==>Summary
2. Group by your Label column
3. Assuming the output data table is sorted from low to high, create a new column called "Rank" and give it the formula........Row()
4. Run Tables==>Update to join the summary table back to the original table, matching on the Label column
Create a new Rank formula column
1. Sort the data by Label
2. Create a new column called Rank with the formula
```
If( Row() == 1,
	count = 1,
	If( :Label != Lag( :Label ),
		count
		++)
);
count;
```
3. 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.

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 ) );

All comments

txnelson

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
1. Run Tables==>Summary
2. Group by your Label column
3. Assuming the output data table is sorted from low to high, create a new column called "Rank" and give it the formula........Row()
4. Run Tables==>Update to join the summary table back to the original table, matching on the Label column
Create a new Rank formula column
1. Sort the data by Label
2. Create a new column called Rank with the formula
```
If( Row() == 1,
	count = 1,
	If( :Label != Lag( :Label ),
		count
		++)
);
count;
```
3. 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.

OneNorthJMP

Hi Jim,

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

txnelson

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

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 ) );

OneNorthJMP

Thanks Jim & MS. It work brilliantly!

BHarris

This is long-since solved, but I found a solution to this yesterday that I figured I'd share in case it's helpful to anyone. Create a new formula column with this formula:

Col Maximum(
	Col Rank(
		If( Row() == Col Minimum( Row(), :Label ),
			:Label,
			.
		)
	),
	:Label
)

Basically this first creates a new array with the label in the row corresponding to its first instance in the original column, and "." everywhere else. Then we ask for the ranking of each label, which results in another array with the label ranking instead of the label itself, and then finally we output an array with the ranking in all rows with that label, and that's what ends up in the formula column.

In my case I was trying to rank groups of sets of data based on their highest value in another column -- e.g. with columns Group, Set, Time, Value, I wanted to find the set's rank within the group based on the highest Value observed across time. Here's a formula that worked:

Col Maximum(
   Col Rank(
      If ( Row() == Col Minimum( Row(), :Group, :Set),
         Col Maximum( :Value, :Group, :Set ),
         .
         ),
      :Group,<BR />      <<tie("row")<BR />      ),
   :Group, :Set
)

I don't know if this is the best way, but it works, and ChatGPT couldn't offer a solution at all, so maybe this will help someone.

hogi

JMP 19:
Add dense ranking to Ranking Tie and Col Rank functions

Col N Categories (:green_heart:)