CODEWITHSUNDEEP
nullkdb+
mmv456
mmv456

Reputation: 23

kdb/Q: How to select rows where at least one column is not null?

I have a table that looks like this:

ID Name 1_Day_Time 2_Day_Time
2B May 762
8D Air 142
W2 Tim 908 332
9H Bee
5E Ice 712

How do I only select those rows with at least one non-null in the 1_Day_Time and 2_Day_Time columns in kdb/Q?

My intended output is below, with the row of ID 9H removed as there are nulls in both 1_Day_Time and 2_Day_Time columns:

ID Name 1_Day_Time 2_Day_Time
2B May 762
8D Air 142
W2 Tim 908 332
5E Ice 712

Upvotes: 0

Views: 370

Answers (3)

cillianreilly
cillianreilly

Reputation: 2076

Another solution that avoids qsql or functional selects. Seems at least as fast as the qsql or functional select answers:

q)t:([]ID:`2B`8D`W2`9H`5E;Name:`May`Air`Tim`Bee`Ice;One_Day_Time:762 0N 908 0N 712;Two_Day_Time:0N 142 332 0N 0N)
q)c:`One_Day_Time`Two_Day_Time
q)t where not all null t c
ID Name One_Day_Time Two_Day_Time
---------------------------------
2B May  762
8D Air               142
W2 Tim  908          332
5E Ice  712

Easy to scale to additional columns also.

Upvotes: 1

Thomas Smyth
Thomas Smyth

Reputation: 5644

If you had many columns to check (instead of 2) you could use this example with a list of columns. The advantage is that is easy to add more columns for consideration quite easily:

q)tab:([]ID:`2B`8D`W2`9H`5E;Name:`May`Air`Tim`Bee`Ice;One_Day_Time:762 0N 908 0N 712;Two_Day_Time:0N 142 332 0N 0N)
q)cls:`One_Day_Time`Two_Day_Time
q)?[tab;enlist(not;(all;enlist,{(null;x)}'[cls]));0b;()]
ID Name One_Day_Time Two_Day_Time
---------------------------------
2B May  762
8D Air               142
W2 Tim  908          332
5E Ice  712

It's also useful if those column names may vary.

Upvotes: 2

scottstein37
scottstein37

Reputation: 306

i'd advise that you not use column names starting with a number (e.g. prefer "One_Day_Time" over "1_Day_Time"), otherwise you'll need functional select. But here are a few possible solutions:

q)show t: ([] ID:`2B`8D`W2`9H`5E; Name:`May`Air`Tim`Bee`Ice; One_Day_Time: 762 0N 908 0N 712; Two_Day_Time: 0N 143 332 0N 0N)
ID Name One_Day_Time Two_Day_Time
---------------------------------
2B May  762
8D Air               143
W2 Tim  908          332
9H Bee
5E Ice  712

q)select from t where not null[One_Day_Time] & null[Two_Day_Time]
ID Name One_Day_Time Two_Day_Time
---------------------------------
2B May  762
8D Air               143
W2 Tim  908          332
5E Ice  712

q)select from t where (not null One_Day_Time) | (not null Two_Day_Time)
ID Name One_Day_Time Two_Day_Time
---------------------------------
2B May  762
8D Air               143
W2 Tim  908          332
5E Ice  712

q)select from t where {not all null x} each (One_Day_Time,'Two_Day_Time)
ID Name One_Day_Time Two_Day_Time
---------------------------------
2B May  762
8D Air               143
W2 Tim  908          332
5E Ice  712

Upvotes: 1

Related Questions