CODEWITHSUNDEEP
kdb+q-lang
user8415577
user8415577

Reputation:

Selecting rows based on column contents which are in a list in kdb

I have a list called 'A' as: 

     cont
     "aa"
     "bb"
      "cc"

I have a table called 'run' containing columns 

      first    second     third
      sad      random      "aa"
      happy    random1     "dd"

I have to select those rows from 'run' where the column third contains elements in list A. I'm very new to kdb and any help on how to do this is appreciated. Thanks

Upvotes: 2

Views: 2284

Answers (1)

Thomas Smyth
Thomas Smyth

Reputation: 5644

You need the keyword in which allows you to check if the values in one list appear in another:

q)show A:("aa";"bb";"cc")
"aa"
"bb"
"cc"
q)show run:([]f:("sad";"happy");s:("random";"random1");t:("aa";"dd"))
f       s         t
----------------------
"sad"   "random"  "aa"
"happy" "random1" "dd"
q)select from run where t in A
f     s        t
-------------------
"sad" "random" "aa"

If A is an unkeyed table, then you can pull the column cont out using A`cont:

q)show A:([]cont:("aa";"bb";"cc"))
cont
----
"aa"
"bb"
"cc"
q)select from run where t in A`cont
f     s        t
-------------------
"sad" "random" "aa"

When using in the output is a boolean list, equal in count to that of the argument on the left (1 2 3 in this case):

q)1 2 3 in 2 4 6
010b

Use of the where clause in the select statement filters the rows returned. Applying where to boolean list returns only the indices where it is true:

q)where 1 2 3 in 2 4 6
,1

And by extension, only these indices would be returned from the table.

It should also be noted that even though the table you have provided is only an example it contains the keyword first as a column header. Keywords as headers should be avoided if possible as they will cause unexpected behaviour.

Upvotes: 2

Related Questions