356621_en-US

liliconcom

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. 

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

 

Mass CustomizationRe: Alternative to "Select duplicate rows"

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

Re: Alternative to "Select duplicate rows"

Thanks, I will try it

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;