topic Re: List the value for Max Date? in Discussions

List the value for Max Date?

UberBock — Fri, 09 Jun 2023 23:36:32 GMT

I have a data table that has a date column, median_resultval column and reagentMLot_sublot . What I would like is that for the Max(Date) what is the value by reagentMLot_sublot. So for each reagentMLot_sublot I would like to list the median_result value for the Max Date and the median_result for the min date.

So if on the min day the median value is 0.1 and on the max day it is 0.2 I would like columns that list that. Issue is that maybe on a date between the min and max it was 0.4.

I tried using the Col Max (median_resultval, reagentMLot_sublot, Col Max(date)) and that didn't work (for the max column). The other column it would be Col Min (median_result, reagentMLot_sublot, Col Min(date)).

Re: List the value for Max Date?

txnelson — Tue, 01 Sep 2020 01:36:24 GMT

This little script will give you a data table with all the min and max date rows with their values of median_resultval

names default to here(1);
dt=current data table();

// Sort the data by reagentMLot_sublot and Date
dtSort = dt << sort(by(:reagentMLot_sublot, :Date),order(ascending,ascending));
dtSort << clear rowstates;

// Loop through the data finding all rows that are not the minimum date or the maximum date
// and delete them
// Row 1 will always be a minimum date, so start with row 2
For( i=2,i<=NRows(dtSort) - 1,i++,
	If( dtSort:reagentMLot_sublot[i] == dtSort:reagentMLot_sublot[i-1] &
		dtSort:reagentMLot_sublot[i] == dtSort:reagentMLot_sublot[i+1],
		Row State( i ) = Selected State( 1 );
	)
);

dtSort << delete rows;

You should be able to take it from there, and display it in the form you want it to be displayed.

Re: List the value for Max Date?

UberBock — Tue, 01 Sep 2020 02:14:10 GMT

txnelson..you are amazing. Here is an example jmp file with what I was trying to do. The much larger table will have 100k more rows so I want to be able to calculate the median range by reagentkit_id. I am looking for an effect where the result val increases over time. By subtracting the min date value from the max date value it will mitigate issues were in data between those dates there may be higher values. In essence screen a large data set for instances where the range (max date data value - min date data values in over certain value). In SQL I can get the min and max calculated for each date but it appears that SQL doesn't allow a max value for max date determination. I had hoped that JMP would. But that doesn't seem to be the case.

Re: List the value for Max Date?

Jeff_Perkinson — Tue, 01 Sep 2020 02:31:19 GMT

I'm sure I don't understand exactly what you're looking for. It might help to see the result you'd like for the table you posted.

However, you might find the JMP Query Builder useful. It does have some of the SQL aggregation functions you may be looking for.

Re: List the value for Max Date?

txnelson — Tue, 01 Sep 2020 02:37:56 GMT

Was your last entry intended to just be a comment? I do not see any request or question?

Re: List the value for Max Date?

UberBock — Tue, 01 Sep 2020 02:42:53 GMT

Sorry.

It was a question in that can JMP, through the use of a column formula, give the col max of a value by the column max of another.

Re: List the value for Max Date?

UberBock — Tue, 01 Sep 2020 03:00:35 GMT

I would like to calculate the range of the median by that value at the max date - the value at the min date.

So in the example the Median range would be 0.10524115 - 0.10920177 (median on min date - median on max date) and not 0.23648086-0.09078947 (median max - median min).

Re: List the value for Max Date?

txnelson — Tue, 01 Sep 2020 03:01:17 GMT

A JMP formula is just a specialized piece of JSL. So almost anything that can be done in JSL, can be done in a formula, by one way or another.

Re: List the value for Max Date?

UberBock — Tue, 01 Sep 2020 03:01:57 GMT

Sorry my bad too late here.

Median on Max date - median on min date

Re: List the value for Max Date?

txnelson — Tue, 01 Sep 2020 03:03:15 GMT

yes