Choose Language Hide Translation Bar
Highlighted
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
Highlighted
ms
Super User ms
Super User

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
Highlighted
ms
Super User ms
Super User

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

Article Labels

    There are no labels assigned to this post.