topic Re: Select last point in each run in Discussions

Select last point in each run

BHarris — Thu, 10 Oct 2019 18:50:00 GMT

Suppose I have columns: run_id, time, distance

... and I want to just select the rows with the last time for each run_id. How do I do that?

Re: Select last point in each run

Mark_Bailey — Thu, 10 Oct 2019 19:46:13 GMT

You can create an indicator column using a formula. Be sure to sort (nested) by run_id and time first. The formula would be a simple Boolean result. Use the Lag() function as such: If( :run_id == Lag( :run_id, -1 ), 0, 1 ). Then you can select the rows where this column equals 1.

Re: Select last point in each run

BHarris — Thu, 10 Oct 2019 20:12:21 GMT

Is this pretty typical in JMP, to create formula columns to look for certain conditions then select based on that?
I played around for a minute after writing this question and ended up with this formula column: Col Maximum ( :Name( "time(s)" ), :run_id ) - :Name( "time(s)" )
... then I just click on one of the rows with "0" and then did a "select matching". I was hoping there was an easier way, like making a summary table with max(time) grouped by run_id, then let me select the rows there, but those rows were selecting all of the data from which the max was calculated, not just the rows that held the max value.

Re: Select last point in each run

txnelson — Thu, 10 Oct 2019 20:16:15 GMT

Just to add something that I hope is obvious to all......for @Mark_Bailey code to work, the data table needs to be sorted ascending on columns run_id and time