Troubleshooting Failure to sort columns with Sort() Command, and excluding least recent duplicates

[#1]

Hello!

I have been trying to create a script that graphs a parameter of a multiple samples from multiple tables now. The samples are named in such a way that it is XX-AA, where XX is the ID/name of the sample, and AA is the timestamp of the sample (day 1,2,3, and so forth). I apologize if I am asking so many questions, I am still quite new to programming in JSL.

I have merged my tables, and then wanted to sort my table by the "Date & Time" column, which is formated in a m/d/y h:m:s format. The goal is to remove duplicates of samples, keeping only the most recent of the duplicates, while the other duplcates are hidden and excluded. From the merge of the tables, my script looks like the following:

dtMerged=dt1<<Concatenate(dt2);

dtmerged<<Sort( By(:Sample ID), Order(Ascending));

dtmerged<<Sort(By(:Date & Time), Order(Ascending));



dtmerged<<Select Where(:Sample ID == :Sample ID[Row()+1]);

dtmerged<<hide and exclude;

For whatever reason, it seems that the sort by the "Date & Time" column does not occur, and as result, I am unsure if my row select actually properly works. Would that method of selection hide and exclude the duplicates where a data enry has more than 1 duplicate? I feel that the reason why the sort won't work is because my column name has the "&" symbol in it.

I greatly appreciate your help!

[#2 · Accepted solution]

The JMP Log shows the following error when running your code

Name Unresolved: Date in access or evaluation of 'Date' , :Date/*###*/

In the following script, error marked by /*###*/
dtMerged = Current Data Table();
dtmerged << Sort( By( :Sample ID ), Order( Ascending ) );
dtmerged << Sort( By( :Date/*###*/ & Time ), Order( Ascending ) );

The issue it is pointing to, is that your column named "Date & Time" is being interpreted by JMP as 2 names(Date and Time)  that you want to be used in a Boolean comparison "&".  What you need to do, is to use a :Name() function to clarify the situation

dtMerged = dt1 << Concatenate( dt2 );

dtmerged << Sort( By( :Sample ID ), Order( Ascending ) );

dtmerged << Sort( By( :Name( "Date & Time" ) ), Order( Ascending ) );

dtmerged << Select Where( :Sample ID == :Sample ID[Row() + 1] );

dtmerged << hide and exclude;

.

[#3]

Thank you so much Jim! That was very silly of me not to have the log open to see that occuring...I feel like an idiot for not doing that.

Now that everything is actually sorted correctly, I can proceed to the next step, which is excluding duplicate pieces of data. My goal was to remove the previous iterations of the duplicates, leaving only the most recent entry of the Sample. Say, for example, I have 2 samples both labelled AA-02 at 5AM, while the other was taken at 7AM of the same day (which is recorded in the "Time & Date" column of the table). I want the table to exclude the 5AM entry, but keep the 7AM entry.

Alternatively, the same could occur with 3 samples, say FJ-01 having 3 iterations: One taken at 5AM, one at 7AM, and one at 10AM. Again, the goal is the only keep the 10AM entry, but to exclude the 5AM and 7AM entry. I tried to make a JSL script by first sorting the table by the Sample ID, then by the Date & Time, then using the Select Where command as seen below:

MergedTable<<Sort( By( :Sample ID ), Order( Ascending ));
MergedTable<<Sort(By(:Name("Date & Time")), Order(Ascending));
MergedTable<<Select Where(:Sample ID == :Sample ID[Row()+1]);
MergedTable<<hide and exclude;

However, no selection occurs, and the log unfortunately doesn't seem to mention any errors. Is there something wrong with my selection criteria that would make it not select?