How to select the highest three cell values in every column?

[#1]

For every column that contains a word, say "Prediction", I want to extract top 3 cell values either greater than 5 or greater than 4.5 (if I get 3 numbers above 5 then I dont want any numbers above 4.5) in a list. Further, picking the one that is farthest away from 5 and hide the corresponding cell value in its raw table. For example, in this example table given below:

In Prediction B column, I want to store 6.6, 5.3 and 5. And hide the cell adjacent to Prediction B nut in Column B i.e. cell value 65. 

 

My code selects columns which have the string "Prediction" in it. 

dt = Current Data Table();
a = "Prediction";
col = dt << get column names( string );
nc = N Items( col );
For( i = 1, i <= nc, i++,
	If( Contains( col[i], a ), )
);

How can I choose the top 3 cells greater than 5 or 4.5? Thank you so much JMP community for being so prompt. 

[#2]

The column rank function (and some IF statements) might do the trick.

[#3]

@dale_lehman well, I couldn't find much on how I can use the column rank function. I can extract out one maximum value but I still can't figure how I can go ahead with top 3. 

[#4 · Accepted solution]

I've attached your example with an attempt to use the column B predictions as you specified.  I'm not sure this does exactly what you want, but I think it is close.

[#5]

@dale_lehman Thanks for the help. I have updated my script to select the maximum value of every column and have saved it in the "val" variable. Do you know how I can exclude the cell with the maximum number in the first column, perform some operations then exclude the cell with maximum number in second column and so forth? 

dt = Current Data Table();
col = dt << get column names( string );
nc = N Items( col );
For( i = 1, i <= nc, i++,
	Col1 = Column( dt, i );
	If( Contains( col[i], "Prediction" ),
		Show( col[i] );
		val = Abs( Col Max( Col1 ) );
		Show( val );
		MaxRow = dt << get rows where( As Column( Col1 ) == Abs( Col Max( Col1 ) ) );
	);
);

[#6]

I don't do much scripting so I'll leave that for someone else to help you with.  I'll only say that I don't really understand the reason to want to exclude rows after doing these operations - it would seem better to create new columns with those rows either excluded or set to missing.  I would think that script would be relatively straightforward and I don't like the idea of overwriting the original data (if that is what you are suggesting).

[#7]

This is a way of checking for data that needs to be removed from my analysis so I have to do it the same way. Thanks a lot for all the help I really appreciate it @dale_lehman

[#8]

@amy, 
           Are you looking to exclude the entire row or the row for that column only ? The reason I ask, I am pretty sure you can exlude the whole row by using: 

dt << Exclude ;

          However, if you just want to exclude the row in a column, I dont think it is possible. So why don't you try to store the row numbers into a variable and then exclude those rows ? 

[#9]

I don't even want to exclude an entire row. I want to exclude only cells. For Example, in the table I have posted, I want to be able to exclude the cell in column "B" that is adjacent to the cell that has value 6.6 in column "Prediction B". I am still struggling to figure how I can use regex to match such strings. I have made 2 lists, one which contains col names that have the word "Prediction" in them and another that dont. I would appreciate if you could also point me out to any function you think would work out best with such type of tables. I have just started working with JSL and I am still struggling with it. Thanks in advance for the help. @uday_guntupalli

P.S: Does that mean there is no way I can exclude a single cell from a column in JSL?

[#10]

In JMP you do not "Exclude" an individual cell, rather, you set the cell's value to a "Missing Value".  The Missing Value's symbol is "."

Of if the column is of a character Data Type, you set the value to ""