Choose Language Hide Translation Bar
Highlighted
ileshem
Level III

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

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. 

 

RowDate and timeNameTest DurationDate
18/23/20 6:12 PMRoi858/23/2020
28/23/20 1:54 PMDan738/23/2020
38/23/20 9:21 AMRobert768/23/2020
48/18/20 6:12 PMRoi858/18/2020
58/18/20 4:57 PMRoi1608/18/2020
68/18/20 4:49 PMRoi1688/18/2020

 

Thanks in advance. 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
txnelson
Super User

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

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);
Jim

View solution in original post

1 REPLY 1
Highlighted
txnelson
Super User

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

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);
Jim

View solution in original post

Article Labels