Subscribe Bookmark RSS Feed

How to find the minimum value of selected rows within a column?

natalie_

Community Trekker

Joined:

Jan 6, 2016

Hi all,

I am writing a script, and I need to search for the minimum value in a column but only within a few rows.  Is there a way to do select these rows using the minimum function?

I thought about creating a subset and then using the minimum function, but I was wondering if there was a simpler way.

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Solution

The following code will do the trick.

dt = open("$sample_data\Big Class.jmp");

selected_rows = dt << get selected rows;

if (nrows(selected_rows) == 0,

      selected_rows = 1::nrows(dt);

);

selected_values = :height[selected_rows];

min_value = min(selected_values);

8 REPLIES
txnelson

Super User

Joined:

Jun 22, 2012

You can find the minimum of selected rows using the below

TheMinimum = col minimum(if(selected(Rowstate())==1,<Place your column name here>,.));

Jim
natalie_

Community Trekker

Joined:

Jan 6, 2016

Hi Jim,

I don't understand how these functions are working together.  I see that Row State Functions can have 6 characteristics; one of them being ​Selected​.  Setting it equal to one means that something has been selected (I think), but how do I specify which rows?  I tried selecting rows, but it didn't work...

minimum = dtFile1<<col minimum(if(selected(Rowstate(Select Rows(Index(41,60)))==1,"d2Id/dVd2",,.)));

Solution

The following code will do the trick.

dt = open("$sample_data\Big Class.jmp");

selected_rows = dt << get selected rows;

if (nrows(selected_rows) == 0,

      selected_rows = 1::nrows(dt);

);

selected_values = :height[selected_rows];

min_value = min(selected_values);

natalie_

Community Trekker

Joined:

Jan 6, 2016

Thank you, your solution worked well for me.

selected_rows = 41::60(dtFile1);

selected_values=dtFile1:d2IddVd2[selected_rows];

minimum = min(selected_values);

txnelson

Super User

Joined:

Jun 22, 2012

The statement I gave you assumes that you have interactively selected the rows.  Therefore, it is that action the statement I gave you is acting on.

minimum = col minimum(if(selected(Rowstate) == 1, ZIPPY, . ));

The value of the variable MINIMUM will contain the minimum value found in the column ZIPPY for all rows that have been selected when the statement is executed.

The interpretation of the code, is the result of the internal IF statement.  As the COL MINIMUM function is run, it goes through each row in the data table.  If the row is selected, the value returned value from the SELECTED function when it looks at the ROWSTATE for that row, will equal 1, therefore it will use the value for the column ZIPPY for that row in its determination of the minimum.  If the row isn't SELECTED, it will us a missing value for the value of ZIPPY for that row.

So all you have to do is to replace "ZIPPY" with the name of the column you are attempting to get the minimum value for the selected rows.

If you are not going to have the user of the program selecting the rows to act on, then a further discussion on using the col<<select rows where() function needs to take place

Jim
natalie_

Community Trekker

Joined:

Jan 6, 2016

Thank you Jim, excellent explanation.

ms

Super User

Joined:

Jun 23, 2011

Just adding to Jim's explanation that his clever formula can be written in a less explicit way, which also may be easier to understand.

These two lines of code are essentially the same. You'll find the latter faster for large tables.

TheMinimum = Col Minimum(If(Selected(Row State()) == 1, height, .));


TheMinimum = Col Minimum(If(Selected(), height));

natalie_

Community Trekker

Joined:

Jan 6, 2016

Thank you, I will keep this in mind!