cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
tgp109
Level I

How to delete rows based upon a condition

I have a joined data table and i would like to select & delete rows if two condition are both simultaneously met.

Character Condition1= Missing a name in column ("NAME")

Character Condition2= Character value in column ("NO") is L9% & exists where there are various character values in the table (ie. L921000, L939456, L999999, etc.)

Is there a way to do this since i don't think i can use an sql wildcard% or do i need to change this column to a number (by removing the L & changing it from character to numeric) and then i can chose values >899,999.

The error is in the 2nd part of my select where statement dealing with condition 2 (L9 character values)

 


DATA3=Data Table( "DATA1" ) << Join(
With( Data Table( "DATA2" ) ),
Merge Same Name Columns,
By Matching Columns( :NO = :NO ),
Drop multiples( 1, 1 ),
Include Nonmatches( 1, 1 ),
Preserve main table order( 1 ),
Output Table( "MERGE3" )
);

 

DATA3 << select where((Is missing(:NAME)) & contains(as column(DATA3,"NO"),"L9")==2) << delete rows << clear select;

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ms
Super User (Alumni) ms
Super User (Alumni)

Re: How to delete rows based upon a condition

Try the Left() or Starts With() function.

 

Example:

DATA3 << select where(Is Missing(:NAME) & Starts With(:NO, "L9")) << delete rows;

 

View solution in original post

1 REPLY 1
ms
Super User (Alumni) ms
Super User (Alumni)

Re: How to delete rows based upon a condition

Try the Left() or Starts With() function.

 

Example:

DATA3 << select where(Is Missing(:NAME) & Starts With(:NO, "L9")) << delete rows;