CODEWITHSUNDEEP
kdb+
delita
delita

Reputation: 1581

Select non-empty string rows in KDB

q)tab

items sales prices detail
-------------------------
nut   6     10     "blah"    
bolt  8     20     ""
cam   0     15     "some text"    
cog   3     20     ""    
nut   6     10     ""    
bolt  8     20     ""

I would like to select only the rows which have "detail" non-empty. Seems fairly straightforward, but not i can't get it to work.

q) select from tab where count[detail] > 0

This gives all the rows still.

Alternatively i tried

q) select from tab where not null detail

This gives me type error.

How can one query for non-empty string fields in KDB???

Upvotes: 2

Views: 7063

Answers (5)

nyi
nyi

Reputation: 3229

If your table is not big, another way you can check is by converting it to a symbol in the where clause. 

q)select from ([]detail:("blah";"";"some text")) where `<>`$detail 
detail
-----------
"blah"
"some text"

Or simply 

q)select from ([]detail:("blah";"";"some text")) where not null `$detail 
detail
-----------
"blah"
"some text"

Upvotes: 0

DanDan4561
DanDan4561

Reputation: 393

Rather than use adverbs, you can simplify this with the use of like. 

q)select from tab where not detail like ""

  items sales prices detail
  ------------------------------
  nut   1     10     "blah"
  cam   5     9      "some text"

Upvotes: 4

Anton Dovzhenko
Anton Dovzhenko

Reputation: 2569

I would use following approach

select from tab where not detail~\:""

where every detail is compared to empty string. Approach with not null detail does not work, because Q treats string as character array and checks if each of array elements is null. I.e. null "abc" returns boolean array 000b, but where clause expects for single boolean value for each "row"

Upvotes: 1

jomahony
jomahony

Reputation: 1692

Use the adverb each:

q)select from ([]detail:("blah";"";"some text")) where 0<count each detail
detail     
-----------
"blah"     
"some text"

Upvotes: 1

JejeBelfort
JejeBelfort

Reputation: 1663

As you need to perform the check row-wise, use each:

select from tab where 0 < count each detail

This yields to the following table:

items sales prices detail     
------------------------------
nut   6     10     "blah"     
cam   0     15     "some text"

Upvotes: 1

Related Questions