Getting rows with different values from another table

Community Support
1

Hello, wondering if someone can help me with this query, I can’t seem to get it.

I have two keyed tables.  I would like to get all the rows from the first table that have different values in one column than rows in the second table under the same column.

example, I only want rows that have different values in the `one column

table1

symonetwo

“A"3"t”

“B"3"t”

“C"4"t”

“D"3"a”

table2

symonetwo

“A"3"t”

“B"3"t”

“C"3"t”

“D"3"t”

q)table1 except table2

returns

onetwo

4"t"

3"a"

and I just want

onetwo

4"t"

but I’m not sure how to get that

2

q)t1:([sym:“ABCD”]one:3 3 4 3;two:“ttta”)<o:p></o:p>

q)t2:([sym:“ABCD”]one:3 3 3 3;two:“tttt”)<o:p></o:p>

q)select from t1 where one<>t2[(sym);`one]<o:p></o:p>

sym| one two<o:p></o:p>

—| -------<o:p></o:p>

C  | 4   t<o:p></o:p>

<o:p> </o:p>

So lookup your t1 sym in t2, return the ‘one’ column from t2, and compare it to the ‘one’ column in t1<o:p></o:p>

<o:p> </o:p>

or<o:p></o:p>

<o:p> </o:p>

q)select from t1 where not (sym;one)in select sym,one from t2<o:p></o:p>

sym| one two<o:p></o:p>

—| -------<o:p></o:p>

C  | 4   t<o:p></o:p>

q) value select from t1 where not (sym;one)in select sym,one from t2<o:p></o:p>

one two<o:p></o:p>

-------<o:p></o:p>

4   t<o:p></o:p>

<o:p> </o:p>

Create a new table (sym;one) from columns in t1 and check each row against the table “select sym,one from t2”<o:p></o:p>

<o:p> </o:p>

First method is a bit faster.<o:p></o:p>

<o:p> </o:p>

From: personal-kdbplus@googlegroups.com [mailto:personal-kdbplus@googlegroups.com] On Behalf Of Roni Hoffman
Sent: Wednesday, June 10, 2015 11:54 AM
To: personal-kdbplus@googlegroups.com
Subject: [personal kdb+] Getting rows with different values from another table<o:p></o:p>

<o:p> </o:p>

Hello, wondering if someone can help me with this query, I can’t seem to get it.<o:p></o:p>

<o:p> </o:p>

I have two keyed tables.  I would like to get all the rows from the first table that have different values in one column than rows in the second table under the same column.<o:p></o:p>

<o:p> </o:p>

example, I only want rows that have different values in the `one column<o:p></o:p>

<o:p> </o:p>

table1<o:p></o:p>

<o:p> </o:p>

sym      one      two<o:p></o:p>

“A”      3          “t”<o:p></o:p>

“B”      3          “t”<o:p></o:p>

“C”      4          “t”<o:p></o:p>

“D”      3          “a”<o:p></o:p>

<o:p> </o:p>

table2<o:p></o:p>

<o:p> </o:p>

sym      one      two<o:p></o:p>

“A”      3          “t”<o:p></o:p>

“B”      3          “t”<o:p></o:p>

“C”      3          “t”<o:p></o:p>

“D”      3          “t”<o:p></o:p>

<o:p> </o:p>

<o:p> </o:p>

q)table1 except table2<o:p></o:p>

<o:p> </o:p>

returns<o:p></o:p>

<o:p> </o:p>

one      two<o:p></o:p>

4          “t”<o:p></o:p>

3          “a”<o:p></o:p>

<o:p> </o:p>

and I just want<o:p></o:p>

<o:p> </o:p>

one      two<o:p></o:p>

4          “t”<o:p></o:p>

<o:p> </o:p>

but I’m not sure how to get that<o:p></o:p>

–
Submitted via Google Groups

3

Thank you very much David

4

Hi David, I am trying to do something similar now but can’t figure out this type error.

I have a table with times in it

closes:([sym:`$()]time:“T”$())

closes insert (BAC;15:00:00.00)

closes insert (GE;16:00:00.00)

and trade table and trying to get sum between times

select dayvol:sum size by sym from trade where date=2015.07.10, time within 09:30:00.00 closes[(sym);`time]

not sure what I’m doing wrong, looks the same as yours..

5

The syntax is a little weird in this case:<o:p></o:p>

<o:p> </o:p>

q)trade:(date:2015.07.10;sym:`GE;time:09:20+1t*til 12;size:12?10f)<o:p></o:p>

q)select dayvol:sum size by sym from trade where date=2015.07.10, time within’flip(count[time]#09:30:00.00;closes[(sym);`time])<o:p></o:p>

sym| dayvol<o:p></o:p>

—| --------<o:p></o:p>

GE | 27.04624<o:p></o:p>

<o:p> </o:p>

Or you could go:<o:p></o:p>

q)select dayvol:sum size by sym from trade where date=2015.07.10,time>=09:30,time<=closes[(sym);`time]<o:p></o:p>

sym| dayvol<o:p></o:p>

—| --------<o:p></o:p>

GE | 27.04624<o:p></o:p>

<o:p> </o:p>

Which is clearer but probably a bit slower.<o:p></o:p>

<o:p> </o:p>

From: personal-kdbplus@googlegroups.com [mailto:personal-kdbplus@googlegroups.com] On Behalf Of Roni Hoffman
Sent: Friday, July 17, 2015 11:51 AM
To: personal-kdbplus@googlegroups.com
Subject: [personal kdb+] Re: Getting rows with different values from another table<o:p></o:p>

<o:p> </o:p>

Hi David, I am trying to do something similar now but can’t figure out this type error.<o:p></o:p>

<o:p> </o:p>

I have a table with times in it<o:p></o:p>

<o:p> </o:p>

closes:([sym:`$()]time:“T”$())<o:p></o:p>

closes insert (BAC;15:00:00.00)<o:p></o:p>

closes insert (GE;16:00:00.00)<o:p></o:p>

<o:p> </o:p>

and trade table and trying to get sum between times<o:p></o:p>

<o:p> </o:p>

select dayvol:sum size by sym from trade where date=2015.07.10, time within 09:30:00.00 closes[(sym);`time]<o:p></o:p>

<o:p> </o:p>

not sure what I’m doing wrong, looks the same as yours..<o:p></o:p>

<o:p> </o:p>

On Wednesday, June 10, 2015 at 2:53:58 PM UTC-4, Roni Hoffman wrote:<o:p></o:p>

Hello, wondering if someone can help me with this query, I can’t seem to get it.<o:p></o:p>

<o:p> </o:p>

I have two keyed tables.  I would like to get all the rows from the first table that have different values in one column than rows in the second table under the same column.<o:p></o:p>

<o:p> </o:p>

example, I only want rows that have different values in the `one column<o:p></o:p>

<o:p> </o:p>

table1<o:p></o:p>

<o:p> </o:p>

sym  one      two<o:p></o:p>

“A”  3          “t”<o:p></o:p>

“B”   3          “t”<o:p></o:p>

“C”   4          “t”<o:p></o:p>

“D”  3          “a”<o:p></o:p>

<o:p> </o:p>

table2<o:p></o:p>

<o:p> </o:p>

sym  one      two<o:p></o:p>

“A”  3          “t”<o:p></o:p>

“B”   3          “t”<o:p></o:p>

“C”   3          “t”<o:p></o:p>

“D”  3          “t”<o:p></o:p>

<o:p> </o:p>

<o:p> </o:p>

q)table1 except table2<o:p></o:p>

<o:p> </o:p>

returns<o:p></o:p>

<o:p> </o:p>

one   two<o:p></o:p>

4      “t”<o:p></o:p>

3      “a”<o:p></o:p>

<o:p> </o:p>

and I just want<o:p></o:p>

<o:p> </o:p>

one   two<o:p></o:p>

4      “t”<o:p></o:p>

<o:p> </o:p>

but I’m not sure how to get that<o:p></o:p>

–
Submitted via Google Groups

6

I see, works, yea I’ll go with the clearer way first…
Thanks for all your help!