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

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

miguello — Thu, 15 Aug 2024 22:22:59 GMT

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?

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

txnelson — Thu, 15 Aug 2024 23:14:00 GMT

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

jthi — Fri, 16 Aug 2024 04:10:12 GMT

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:

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

hogi — Sat, 17 Aug 2024 08:03:20 GMT

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 byCol

hogi — Fri, 16 Aug 2024 15:56:06 GMT

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

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

miguello — Fri, 16 Aug 2024 20:36:58 GMT

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

miguello — Fri, 16 Aug 2024 20:43:20 GMT

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

jthi — Sat, 17 Aug 2024 05:07:59 GMT

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

hogi — Sat, 03 May 2025 15:03:23 GMT

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

**) if Jarmo's wish gets enough Kudos: