430981_en-US

liliconcom

Script for removing duplicate rows

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:

 

Row	Date	A	B	C	D
1	22.10.2021	1	10	4	15
2	23.10.2021	2	15	8	27
3	26.10.2021	3	2	5
4	25.10.2021	4	7	13	12
5	26.10.2021	3	2	5	6

 

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

 

 

Automation and ScriptingData Blending and CleanupRe: Script for removing duplicate rows

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.

Re: Script for removing duplicate rows

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

Re: Script for removing duplicate rows

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

Re: Script for removing duplicate rows

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).