topic Re: Alternative to "Select duplicate rows" in Discussions

Alternative to "Select duplicate rows"

Stas — Sat, 10 Jun 2023 23:25:50 GMT

Hey guys,
I'm looking for an alternative to the "Select duplicate rows" function that does not exist in JMP12.
My aim is to delete duplicate rows by column value, with an exception of specific value

For example, if I have this my data table, I want to leave the first substances of column "Color", With an exception that if the "Color" column value is Yellow, leave all these rows and don't delete them.

Eventually I will stay with one fruit of each color, except yellow fruits that will all stay.

Fruit	Color	Number
Apple	Red	2
Strawberry	Red	3
Lemon	Yellow	2
Banana	Yellow	4
Orange	Orange	5
banana	Green	4
Apricot	Orange	5
Watermelon	Green	4

Re: Alternative to "Select duplicate rows"

jthi — Sat, 06 Feb 2021 08:55:17 GMT

Idea from here: Re: Eliminating Duplicate Rows (keeping first duplicate)

I only have JMP15 installed, so I cannot test it this solution will work on JMP12.

Names Default To Here(1);

dt = New Table("Untitled 2",
	Add Rows(10),
	Compress File When Saved(1),
	New Column("Fruit",
		Character,
		"Nominal",
		Set Values(
			{"Apple", "Strawberry", "Lemon", "Banana", "Orange", "banana", "Apricot",
			"Watermelon", "Kiwi", "Lingonberry"}
		),
		Set Display Width(75)
	),
	New Column("Color",
		Character(16),
		"Nominal",
		Set Values(
			{"Red", "Red", "Yellow", "Yellow", "Orange", "Green", "Orange", "Green",
			"Green", "Red"}
		)
	),
	New Column("Number",
		Numeric,
		"Continuous",
		Format("Best", 12),
		Set Values([2, 3, 2, 4, 5, 4, 5, 4, 1, 0])
	)
);

dt << select where(Col Min(Row(), :color) < Row() & :Color != "Yellow")
//dt << Delete Rows;

Re: Alternative to "Select duplicate rows"

Stas — Sat, 06 Feb 2021 10:06:28 GMT

Thanks, I will try it

Re: Alternative to "Select duplicate rows"

txnelson — Sat, 06 Feb 2021 13:09:26 GMT

I tested @jthi elegant solution in JMP 12.2.0 and it worked correctly.