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