new column: rank unique

[#1]

Hi,

I want to create a column with values 1,2,3 - N to label unique values in another column.

In the community, there are several similar posts 

How to create a new column with numerical number based on the unique grouping of column A 

Rank Unique 

with a variety of solutions.

But the one cool solution I did not find up to now.
It should be 

• dynamic (react on changes in the table) -> no tables/Summary, no Label to code
• independent of the column type (at least: Character + Numeric) -> no Label to code
• functional even for non-sorted tables -> no lag
• fast -> no associative array

Best: a short function like  (*)

rank unique(:col1)

or an option of a function  (**)

rank(:col1, << unique(1))

I found the option 

rank(:col1, << tie(mode))

but I did not find a mode which produces number 1, 2,3 ... N

I thought that one can get there by using rank twice:

New Column( "step1",Formula( Col Rank( :sex, <<tie( "average" ) ) ));
New Column( "step2",Formula( Col Rank( :step1 ) ));

But step2 knows some secrets about column step1 which the user can't see:

What works:

New Column( "Rank[sex]",
	Formula(
		If(row() == 1,summarize(values= by(:sex)));
		Contains( values, :sex )
	)
);

I am still searching for (*) or (**) and hope that I can find it in the New Formula column right click context menu. But where?

[#2 · Accepted solution]

There isn't single Col function that can perform this task. You can do this with Col functions, but it will most likely require few of  those and be slower than using Summarize (or Associative Array).Add dense ranking to Ranking Tie and Col Rank functions 

[#3]

A pity with the Wish list entries - there are so good ones - but they stay unnoticed.
How could I have missed this one ...
-> Kudo from my side.

[#4]

Search isn't the best one in JMP community, so I should have added more "keywords" to title, such as the unique ranking. This is a problem that I face from time to time at work, but luckily (at least not yet...) either the calculation speed isn't an issue or I don't need dynamic formula.

[#5]

search: Thank you for your post on usin the community.
Sine that time, I follow your suggestion and always use Google search - I even added a shortcut to my browser.

In the past, a good approach was to send a post to the community, then open it and look at the suggestions of "related posts" on the right - but since the update of the community layout I can't find this feature anymore.

A nice application for Rank unique:
In Graph Builder when I put wafers into the Group X drop zone and variants into the Group Y drop zone, if it's no DOE and every wafer has just one variant, I get a very sparse plot with a lot of white area.
Then it's cool to have the rank 1,2,3 ... for the Group X drop zone and the wafer info just via Hover label.

It's not really essential to have the dynamic version - but I have a better feeling if the values adjust automatically after some table update - such that it's guaranteed that there are no old values.
Associative Arrays get very slow already for decent length of data. really dangerous if did not save your data ...

[#6]

Hm, still the same in Jmp 18:

Following an idea of @jthi from Add Counter for Unique Cases in Groups :

Names Default to Here(1);
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
New Column( "rank_unique (sex) by age",
	Formula(
		Col Minimum(
			Col Rank( If( Row() == Col Min( Row(), :age, :sex ), 1 ), :age ),
			:age,
			:sex
		)
	)
)

• just a value for the first of each sex X age
• generate rank 
• use Col Minimum to broadcast the values to the other rows