cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Choose Language Hide Translation Bar
Stas
Level I

Alternative to "Select duplicate rows"

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. 

FruitColorNumber 
AppleRed2 
StrawberryRed3 
LemonYellow2 
BananaYellow4 
OrangeOrange5 
bananaGreen4 
Apricot Orange5 
WatermelonGreen4 
    

 

3 REPLIES 3
jthi
Super User

Re: Alternative to "Select duplicate rows"

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;
-Jarmo
Stas
Level I

Re: Alternative to "Select duplicate rows"

Thanks, I will try it

txnelson
Super User

Re: Alternative to "Select duplicate rows"

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

Jim