782021_en-US

liliconcom

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

I use Col Rank() to count runs:

Col Rank( :DateTime, :WaferID, <<TIE></TIE>

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?

Basic Data Analysis and ModelingRe: 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) );

 

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

 

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

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!

Re: How to tweak Col Rank() to give consecutive numbers when using byColAnd if you have 5 more seconds, please also vote for:
https://community.jmp.com/t5/JMP-Wish-List/Col-N-Categories/idc-p/782236#M6115Re: 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 )

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

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.

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.

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></TIE>

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

**) if Jarmo's wish gets enough Kudos: Add dense ranking to Ranking Tie and Col Rank functions