topic Re: How to find the minimum value of selected rows within a column? in Discussions

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

natalie_ — Thu, 21 Jan 2016 16:55:44 GMT

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!

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

txnelson — Thu, 21 Jan 2016 18:45:25 GMT

You can find the minimum of selected rows using the below

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

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

natalie_ — Thu, 21 Jan 2016 21:06:27 GMT

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",,.)));

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

pmroz — Thu, 21 Jan 2016 21:28:02 GMT

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

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

txnelson — Thu, 21 Jan 2016 21:50:57 GMT

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

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

natalie_ — Thu, 21 Jan 2016 22:11:50 GMT

Thank you, your solution worked well for me.

selected_rows = 41::60(dtFile1);

selected_values=dtFile1:d2IddVd2[selected_rows];

minimum = min(selected_values);

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

natalie_ — Thu, 21 Jan 2016 22:33:11 GMT

Thank you Jim, excellent explanation.

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

ms — Fri, 22 Jan 2016 09:00:59 GMT

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

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

natalie_ — Fri, 22 Jan 2016 14:27:04 GMT

Thank you, I will keep this in mind!