Hello all,
I’m learning Q and KDB, started few days ago.
I checked several sources, but I am not able to search for a list in a table.
I have this table:
ctable:(v:((0.0 0.0);(1.0 0.0);(0.0 1.0)))
then if I try something like this
select from ctable where (0.0 0.0) in v
I got an empty result
How can I do this select ?
thanks,
Fausto
q)ctable:(v:((0.0 0.0);(1.0 0.0);(0.0 1.0)))
Simple trick, not using select -
q)ctable[ctable?(0f;1f)]
v| 0 1
q)ctable[ctable?(0f;1f)][`v]
0 1f
Kumar
You can do this to find all instances of 0 0f in the table:
For example:
q)ctable:(v:(1000?((0.0 0.0);(1.0 0.0);(0.0 1.0))))
q)// Pass each row of column v into the function and check if it’s = (0 0f)
q)ctable where {all x = (0 0f)}each ctable`v
v
0 0
0 0
0 0
0 0
…
Aidan
Another option:
q)select from ctable where v~:0.0 0.0<o:p></o:p>
v<o:p></o:p>
—<o:p></o:p>
0 0<o:p></o:p>
0 0<o:p></o:p>
0 0<o:p></o:p>
0 0<o:p></o:p>
0 0<o:p></o:p>
..
Here the ?each-left? adverb is used (http://code.kx.com/wiki/Reference/BackSlashColon) to iterate over the ?v? column<o:p></o:p>
<o:p> </o:p>
Hope this helps<o:p></o:p>
Jonathon<o:p></o:p>
<o:p> </o:p>
From: aidan.ogorman.aquaq@gmail.com
Sent: 17 February 2017 09:31
To: Kdb+ Personal Developers
Subject: [personal kdb+] Re: query for a list in a table
<o:p> </o:p>
You can do this to find all instances of 0 0f in the table: <o:p></o:p>
<o:p> </o:p>
For example:<o:p></o:p>
<o:p> </o:p>
q)ctable:(v:(1000?((0.0 0.0);(1.0 0.0);(0.0 1.0))))<o:p></o:p>
q)// Pass each row of column v into the function and check if it’s = (0 0f)<o:p></o:p>
q)ctable where {all x = (0 0f)}each ctable`v <o:p></o:p>
v<o:p></o:p>
—<o:p></o:p>
0 0<o:p></o:p>
0 0<o:p></o:p>
0 0<o:p></o:p>
0 0<o:p></o:p>
…<o:p></o:p>
<o:p> </o:p>
Aidan<o:p></o:p>
On Friday, February 17, 2017 at 8:34:41 AM UTC, Fausto Saporito wrote: <o:p></o:p>
Hello all,
I’m learning Q and KDB, started few days ago.
I checked several sources, but I am not able to search for a list in a table.I have this table:
ctable:(v:((0.0 0.0);(1.0 0.0);(0.0 1.0)))
then if I try something like this
select from ctable where (0.0 0.0) in v
I got an empty result
v
How can I do this select ?
thanks,
Fausto<o:p></o:p>
–
Submitted via Google Groups
Correction - my solution would return ONLY the first match . here is a modification for finding all matches.
ctable:(v:(1000?((0.0 0.0);(1.0 0.0);(0.0 1.0))))
where (sum over 't:ctable=:enlist(0f;0f))=count (0f;0f)
Kumar
Hello Aidan,
thanks for the reply.
In this table I should find just one entry for each value I’ll check, so using ? (as proposed in other replies) should be ok.
Fausto
Il giorno venerdì 17 febbraio 2017 10:31:49 UTC+1, aidan.ogo...@gmail.com ha scritto:
You can do this to find all instances of 0 0f in the table:
For example:
q)ctable:(v:(1000?((0.0 0.0);(1.0 0.0);(0.0 1.0))))
q)// Pass each row of column v into the function and check if it’s = (0 0f)
q)ctable where {all x = (0 0f)}each ctable`v
v
0 0
0 0
0 0
0 0
…
Aidan
Hello all,
thanks for the replies!
I defined this function to operate on that table:
Clookup:{`ctable upsert (v:enlist x);ctable?x}
I simplified my logic in this function avoiding select … so the idea, if the list doesn’t exist in the table, add it and return the row where it is.
My question is: Are sql-like statements good in performance, or is it better avoid using them ?
thanks,
Fausto
Il giorno venerdì 17 febbraio 2017 09:34:41 UTC+1, Fausto Saporito ha scritto:
Hello all,
I’m learning Q and KDB, started few days ago.
I checked several sources, but I am not able to search for a list in a table.I have this table:
ctable:(v:((0.0 0.0);(1.0 0.0);(0.0 1.0)))
then if I try something like this
select from ctable where (0.0 0.0) in v
I got an empty result
v
How can I do this select ?
thanks,
Fausto
I know the thread has moved on, but worth mentioning:
q)select from t where(v)in(v:enlist 0 0.)
extends nicely when searching for multiple values
q)select from t where(v)in(v:(0 0.;0 1.))