i have a large dataset that has a static date on a subject (date of chest CT scan) and i need to find the smallest date difference and keep that row (delete the others). jmp 16.2.0
Example below - i want to keep rows 2 and 4 (the smallest day difference for subject 1 and 2).
subj ID. CT date. lung function date. day difference
Created:
Apr 25, 2024 01:00 AM
| Last Modified: Apr 24, 2024 10:00 PM(416 views)
| Posted in reply to message from jsolo01 04-24-2024
Just to extend a little on @WebDesignesCrow interactive solution:
If you don't already have the difference column, you can create it interactively (select both of your dates, right click on the column header and find Difference (reverse order). This will create a new column where the differences are in seconds. If you want to see them in yours/days you can double click on the created column and modify it as needed (such as add division by In Days(1)) but seconds work for this case
Then right click on this new column's header and make absolute value column out of it
After you have that column, right click on the subjectid column and make it grouping column
next you can right click on the date difference column and create rank formula (note the grouping)
from the final column, right click on one of the "ones", select matching cells
From here you can either create subset using selected rows
OR right click on the row list, invert selection and after inversion delete rows
Then if you need to repeat this you can either make a workflow or capture the script from enhanced log
Below is script created by Workflow builder. Only thing it is missing is opening the table as I don't have the table saved (and some column names are incorrect for the same reason) so I added table creation script (workflow is also attached).