topic Re: Script for removing duplicate rows in Discussions

Script for removing duplicate rows

Konstantinos — Fri, 09 Jun 2023 18:04:02 GMT

Hi,

I have a script where I update a table by another one.

For the final table, I would like to remove duplicate rows based on defined columns.

Thereby, the row with the lower row number should be deleted.

In the following you can see an example for better illustration:

For this table, the duplicate rows are defined by the columns :Date, :A and :B.

Row 3 should be deleted and row 5 kept.

Could you provide me with a JSL code for that example?

Many thanks in advance.

Best Regards

Konstantinos

jthi — Thu, 28 Oct 2021 10:33:52 GMT

JMPs interactive Select Dublicate Rows with Delete Rows should be able to do this.

Select columns of interest:

Go to Rows / Row Selection / Select Dublicate Rows:

Right click on row number and Delete Rows:

If you have JMP16 repeat same steps while you have Enhanced log enabled, it will return you the script:

// Delete selected rows
Data Table("Untitled") << Select Duplicate Rows(Match(:Date, :A, :B)) << Delete Rows;

(update the script to use references).

Konstantinos — Thu, 28 Oct 2021 10:47:21 GMT

Thanks for your prompt reply. Unfortunately, that does not fulfill my requirement. I want that row 3 is deleted and not row 5.

jthi — Thu, 28 Oct 2021 11:26:15 GMT

One option would be to sort the data first by Row number -> delete duplicates and then sort again.

jthi — Thu, 28 Oct 2021 11:30:07 GMT

You could also create formula to count unique values. Something like this might work:

Col Number(:Date, :A, :B) - Col Cumulative Sum(1, :Date, :A, :B)

Select all 0 values and invert selection. Then delete rows.