cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Register for our Discovery Summit 2024 conference, Oct. 21-24, where you’ll learn, connect, and be inspired.
Choose Language Hide Translation Bar
miguello
Level VI

How to tweak Col Rank() to give consecutive numbers when using byCol

I use Col Rank() to count runs:

Col Rank( :DateTime, :WaferID, <<Tie( "minimum" ) )

Each run may have multiple rows, which I can only separate by DateTime, so instead of writing long if statements, I used this formula.

 

The only thing that I don't like is the following. Let's say I have three rows generated per run, then my RunID column would look like:

 

1
1
1
4
4
4
7
7
7

 

and so on. I get where it comes from.

My question is - what would be the cleanest way to convert that to effectively 1, 1, 1, 2, 2, 2, 3, 3, 3?

1 ACCEPTED SOLUTION

Accepted Solutions
jthi
Super User

Re: How to tweak Col Rank() to give consecutive numbers when using byCol

I usually use Col Cumulative Sum (or other "tricks" for this)

Col Cumulative Sum(If(Row() == Col Min(Row(), :Column 1), 1,0))

jthi_0-1723781318355.png

 

Wish list:Add dense ranking to Ranking Tie and Col Rank functions 

-Jarmo

View solution in original post

8 REPLIES 8
txnelson
Super User

Re: How to tweak Col Rank() to give consecutive numbers when using byCol

This formula appears to do what you want.  Others may have slicker ways

As Constant( Summarize( bygroup = by( :waferID ) ) );
Contains( bygroup, Char( :waferID) );

 

Jim
jthi
Super User

Re: How to tweak Col Rank() to give consecutive numbers when using byCol

I usually use Col Cumulative Sum (or other "tricks" for this)

Col Cumulative Sum(If(Row() == Col Min(Row(), :Column 1), 1,0))

jthi_0-1723781318355.png

 

Wish list:Add dense ranking to Ranking Tie and Col Rank functions 

-Jarmo
hogi
Level XII

Re: How to tweak Col Rank() to give consecutive numbers when using byCol

Dense ranking - We need it soo often.

 

There might be some JMP users who don't need dense ranking. For users who need it , it always feels like setting up a Turing machine.

For sure, every time it feels great when it works. And you always feel proud when you can show the trick to a colleague who got stuck. 

 

But just imagine how cool it will feel if Dense Ranking is available via new formula column!


So. Please folks, take the 5 seconds, follow Jarmos link to the wish list and vote!

hogi
Level XII

Re: How to tweak Col Rank() to give consecutive numbers when using byCol

And if you have 5 more seconds, please also vote for:
https://community.jmp.com/t5/JMP-Wish-List/Col-N-Categories/idc-p/782236#M6115
miguello
Level VI

Re: How to tweak Col Rank() to give consecutive numbers when using byCol

Thanks a lot for the idea!

 

My specific case is a little but more complicated, I have one or more categorical columns (i.e. different wafers running etc.)

So the formula ended up being this:

Col Cumulative Sum( If( Row() == Col Min( Row(), :Rank, :WaferID ), 1, 0 ), :WaferID )

Ranking.png

..and you have to keep it as two separate columns, but it works!
Thanks!

miguello
Level VI

Re: How to tweak Col Rank() to give consecutive numbers when using byCol

Table has to be sorted by your ranking variable, otherwise, if you have rank 2 before rank 1 - in the RunID they will be 1 and 2, respectively (because Col Cumulative Sum, that's why).

Not critical, but RunID order might be important.

jthi
Super User

Re: How to tweak Col Rank() to give consecutive numbers when using byCol

I consider this to be most reliable way of building this type of ranking in current version of JMP (Col Cumulative Sum) but like you said, data must be sorted. If the original order is important, add row column to your data, sort your data, run the formula for ranking, remove formula, sort back by the row column.

-Jarmo
hogi
Level XII

Re: How to tweak Col Rank() to give consecutive numbers when using byCol

If you don't want to add 2 columns:

Col Cumulative Sum( If( Row() == Col Min( Row(), :Date, :WaferID ), 1, 0 ), :WaferID )

 

If you don't want to sort the data table *) :

Col Maximum( Col Rank( If( Row() == Col Min( Row(), :Date, :WaferID ),:Date), :WaferID) ,: Date, :WaferID )


If you don't want to remember the equation *):

Col Rank(:Date,:WaferID, <<tie("dense"))


*) Sort data table + add rows + concatenate -> make it possible 

*) if Jarmo's wish gets enough Kudos