List the value for Max Date?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)).
Mass CustomizationSharing and Communicating ResultsRe: List the value for Max Date?yes
Re: List the value for Max Date?Sorry my bad too late here.
Median on Max date - median on min dateRe: List the value for Max Date?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?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?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?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?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..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?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.
