topic Re: How to select & colored Top 5 highest value cell from many columns using JSL in Discussions

How to select & colored Top 5 highest value cell from many columns using JSL

WebDesignesCrow — Wed, 19 Jun 2024 04:15:00 GMT

Hello Experts,

How do I select & colored the Top 5 highest value cell from many columns using JSL?

Example:

From all cells in column Type_A to Type_D (the similarity of these column name is it contains "_")

I want to select & colored the Top 5 highest value cell with yellow.

It seems "Select Where" function only applicable to comparing 2 columns.

Expected result should be something like this;

0.14, 0.12,0.11,0.1, 0.9 are the Top 5 highest value cells.

Thank you.

I'm using JMP 17.2

Re: How to select & colored Top 5 highest value cell from many columns using JSL

jthi — Wed, 19 Jun 2024 05:43:30 GMT

How do you wish to deal with ties?

Re: How to select & colored Top 5 highest value cell from many columns using JSL

txnelson — Wed, 19 Jun 2024 05:54:56 GMT

Get the column names for all of the columns you are going to seaarch.

Add a new column that contains the Row number

Stack all of the columns you want to search. Make sure you keep the Row column

Sort the stacked data table in descending order of the Data column

Loop through the first 5 rows of the sorted table and set the cell color in the original table based upon the Label column(column name in original table) and the Row column.

Delete the stacked and sorted data tables

Delete the Row column from the original data table

Re: How to select & colored Top 5 highest value cell from many columns using JSL

WebDesignesCrow — Wed, 19 Jun 2024 06:44:13 GMT

Hi @jthi ,

I thought we have formula like =Top(5, column("Type_A"):column("Type_D) << Color Cell (3);

Where 5 refers to number of Top values from column Type A to column Type D.

I will try @txnelson solution.

Thanks !

Re: How to select & colored Top 5 highest value cell from many columns using JSL

jthi — Wed, 19 Jun 2024 07:12:59 GMT

There is an option using matrices (interactive solution suggested by Jim is much easier to understand and was the first idea that come to my mind also)

Names Default To Here(1); 

// This table shows how this will behave with ties
// dt = Open("$SAMPLE_DATA/2D Gaussian Process Example.jmp");
// cols_of_interest = {"X1", "X2"};

dt = Open("$SAMPLE_DATA/Pogo Jumps.jmp");
cols_of_interest = {"Yat", "Yee", "Sam"};

m = dt[0, cols_of_interest];
v = Shape(m, N Items(m));
r = Ranking(v); // let Ranking decide our top5
idx = Loc(r > N Items(m) - 5);

vals = J(1, N Items(m), .);
vals[idx] = 1;
vals = Shape(vals, N Items(m) / N Items(cols_of_interest));

For(i = 1, i <= N Cols(vals), i++,
	rows_to_color = Loc(vals[0, i]);
	Column(dt, cols_of_interest[i]) << Color Cells("Yellow", rows_to_color)
);

Write();

Pogo Jumps.jmp - Yat, Yee, Sam

2D Gaussian Process Example.jmp - X1, X2 (note the behavior with ties)