JSL, Remove duplicate rows by looking at another column condition.

ileshem — Fri, 09 Jun 2023 23:35:44 GMT

Hello again,
I am looking to remove duplicate rows by looking at another column condition.
I want JSL to remove the duplicate "Roi" rows when they appear in the same Date and keep only the latest row by time (on Date and time column) so the end result will be table with only rows 1,2,3,4 and rows 5 and 6 that appear with same date as row 4 but the time is earlier will be deleted.

Row	Date and time	Name	Test Duration	Date
1	8/23/20 6:12 PM	Roi	85	8/23/2020
2	8/23/20 1:54 PM	Dan	73	8/23/2020
3	8/23/20 9:21 AM	Robert	76	8/23/2020
4	8/18/20 6:12 PM	Roi	85	8/18/2020
5	8/18/20 4:57 PM	Roi	160	8/18/2020
6	8/18/20 4:49 PM	Roi	168	8/18/2020

Thanks in advance.

Re: JSL, Remove duplicate rows by looking at another column condition.

txnelson — Sun, 23 Aug 2020 21:18:57 GMT

Here is a piece of JSL that will do what you want..

names default to here(1);

// Point to the data table
dt=current data table();

// The code below is going to sort the data into a possible different
// order, and by adding a columns that shows the original position
// the data can be sorted back to the original position at the end
dt<<new column("RowNum",formula(Row()));
dt:RowNum << delete formula;

// Sort the data so the latest Date and Time value comes first
dt << sort( by(:Date and time), order(descending),replace table(1));

// Select all rows that are found to be duplicates.  The initial
// rows in the duplicate series are not selected
dt << select duplicate rows( Match(:Name, :Date) );

// Delete all rows found to be duplicates
try( dt << delete rows );

// Sort the data back to the original order
dt << sort( by(:RowNum), order(ascending), replace table(1));

// Delete the RowNum column
dt << delete columns(:rownum);

topic JSL, Remove duplicate rows by looking at another column condition. in Discussions

JSL, Remove duplicate rows by looking at another column condition.

Re: JSL, Remove duplicate rows by looking at another column condition.