Subscribe Bookmark RSS Feed

How to select individual cell based on condition?

davyvy

Community Member

Joined:

Sep 11, 2016

How to select individual cell instead of entire rows?

In table below, I need to select the two -99 cells under Value1 and Value2 column, and delete them. Can someone help? When I used select where, the entire row is selected.

test1Value1test2Value2
2531
354-99
4-995443
-8876234
779-763
506545

Thanks

4 REPLIES
txnelson

Super User

Joined:

Jun 22, 2012

Selecting a single cell, in the context of "Select Where" isn't really available.  But addressing a single cell can be done and then the cell value can be changed, or the cell color can be changed.  Here is an example of how to do this:

Names Default to Here(1);

dt=current data table();

dt:Value 2 << color cells("Red", dt<<get rows where(dt:Value 2 == -99));

Jim
Phil_Brown

Super User

Joined:

Mar 20, 2012

davyvy​,

One approach is to simply clear the value of the cell and replace it with missing value (essentially same as delete). So..


:Value1 << Set Each Value( If( Value1[ ] == -99, ., Value1[ ] ) );

:Value2 << Set Each Value( If( Value2[ ] == -99, ., Value2[ ] ) );


I've found that Set Each Value is quite fast. I ran a  test on a 5 column, 6.6M row table and each of the operations above completed in ~2s.

PDB
jerry_cooper

Staff

Joined:

Jul 10, 2014

Just in case you were looking for a non-scripting approach, you could use search and replace (Ctrl+F), or you could use Column Properties and set -99 as a missing value code. If you use the search option, be sure to leave the Replace With box empty (i.e no space).

ron_horne

Super User

Joined:

Jun 23, 2011

alternatively, you can use the recode option to change the -99 to missing values or whatever value you want. just right click the column from the column list on the left and select recode. the rest is self explanatory.