kdb

KDB/Q matching events with imprecise timestamps

I have tables that contain events identified by a ts, a symbol and another symbol like column. The timestamps are not precise and I am trying to match via wj to get all possible matches within a time window.

t1: ([] ts: 10:00:01.03 10:00:03.16 10:00:03.16; sym: `A`B`A; ex: `a`b`b; q: 100 200 200)
t2: ([] ts: 10:00:01.00 10:00:01.035 10:00:03.15 10:00:03.155 10:00:03.158 10:00:03.16 10:00:03.165; sym: `A`A`B`B`A`B`A; ex: `a`a`a`b`b`b`a; id: til 7)

enter image description here

enter image description here

wj[-00:00:00.01 00:00:00.1+\:t1`ts;`sym`ex`ts;t1;(t2;(::;`id))]

However, when I match them it seems to only take the time window into account, ignoring matching patterns on sym and ex. Here for example it matches the second event with event id 3,4,5 where 4 has a different symbol

enter image description here

What is my mistake here?

Solution

  • From https://code.kx.com/q/ref/wj, q should be sorted `sym`time with `p# on sym:

    q)wj[-00:00:00.01 00:00:00.1+\:t1`ts;`sym`ex`ts;t1;(`sym`ts xasc t2;(::;`id))]
ts           sym ex q   id
---------------------------
10:00:01.030 A   a  100 0 1
10:00:03.160 B   b  200 3 5
10:00:03.160 A   b  200 ,4