CODEWITHSUNDEEP
nullkdb+
bigO6377
bigO6377

Reputation: 1274

KDB Excluding rows that have nulls

I have a table that has a handful of cells with null values (scattered across the dataset). Any simple way of exluding all rows that have a null in any of its columns?

I'm just trying to avoid this...

select from T where not null col1, not null col2, not null col3, etc...

Upvotes: 4

Views: 16587

Answers (2)

terrylynch
terrylynch

Reputation: 13572

Adding to Ryan's answer.....if your table has string (nested) columns then you'll need to modify the functional form a little:

q)tab:([] col1:`a`b`c`d;col2:1 0N 3 0N;col3:"v vv";col4:0n 1.0 2.0 3.0;col5:("";"";"rf";"er"))

q)tab
col1 col2 col3 col4 col5
------------------------
a    1    v         ""
b              1    ""
c    3    v    2    "rf"
d         v    3    "er"

Then using zero count as the indicator of a null string:

q)?[`tab;not,'enlist each @[(null;0=count');"C"=exec t from meta tab],'cols tab;0b;()]
col1 col2 col3 col4 col5
------------------------
c    3    v    2    "rf"

Upvotes: 3

Ryan Hamilton
Ryan Hamilton

Reputation: 2605

Either use simple list functions for easy to read code or functional form for speed. The functional form will be faster as it doesn't scan all the entire columns just those that pass each stage of the filter.

q)t:flip `a`b`c`d`e!flip {5?(x;0N)} each til 10
q)t
a b c d e
---------
0       0
  1   1
  2 2 2
3 3   3 3
4 4 4 4 4
      5 5
        6
7 7
  8     8
9 9   9 9

// simple way
q)where all each not null t
,4
q)t where all each not null t
a b c d e
---------
4 4 4 4 4

// faster
q)?[t;{(not;(null;x))} each cols t; 0b; ()]
a b c d e
---------
4 4 4 4 4

Upvotes: 8

Related Questions