Select non-empty string rows in KDB

Question

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???

DanDan4561 · Accepted Answer · 2018-09-25 02:39:23Z

4

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"

answered Sep 25, 2018 at 2:39

DanDan4561

3931 gold badge2 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JejeBelfort · Accepted Answer · 2018-09-25 01:30:20Z

1

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"

answered Sep 25, 2018 at 1:30

JejeBelfort

1,6932 gold badges21 silver badges40 bronze badges

Comments

jomahony · Accepted Answer · 2018-09-25 01:31:05Z

1

Use the adverb each:

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

answered Sep 25, 2018 at 1:31

jomahony

1,7028 silver badges9 bronze badges

Comments

Anton Dovzhenko · Accepted Answer · 2018-09-25 02:02:34Z

1

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"

answered Sep 25, 2018 at 2:02

Anton Dovzhenko

2,57913 silver badges16 bronze badges

1 Comment

delita Over a year ago

Thank you for detailed explanation. Now i understand underlying construct better.

nyi · Accepted Answer · 2018-09-25 06:17:15Z

0

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"

answered Sep 25, 2018 at 6:17

nyi

3,2494 gold badges25 silver badges47 bronze badges

Collectives™ on Stack Overflow

Select non-empty string rows in KDB

5 Answers 5

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related