topic Re: new column: rank unique in Discussions

new column: rank unique

hogi — Fri, 15 Sep 2023 12:20:50 GMT

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

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?

Re: new column: rank unique

jthi — Fri, 15 Sep 2023 15:24:34 GMT

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

Re: new column: rank unique

hogi — Fri, 15 Sep 2023 15:28:24 GMT

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.

Re: new column: rank unique

jthi — Fri, 15 Sep 2023 15:41:21 GMT

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.

Re: new column: rank unique

hogi — Fri, 15 Sep 2023 15:55:21 GMT

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

Re: new column: rank unique

hogi — Tue, 23 Jan 2024 22:01:41 GMT

Hm, still the same in Jmp 18:

Following an idea of @jthi from :

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