Hi,
I am trying to find records which are not join in 2 keyed tables. Till now i have reach to the following query.
trade1:([time:time$()]sym:symbol$();price:float$();size:int$())
trade2:([time:time$()]sym:symbol$();price:float$();size:int$())
/Add blank col in trade1
update trade1Col:1 from `trade1
/Add blank col in trade2
update trade2Col:1 from `trade2
(select time,sym,price,size,trade1Col,trade2Col from (trade1 lj trade2) where trade2Col=0N)
uj
select time,sym,price,size,trade1Col,trade2Col from (trade2 lj trade1) where trade1Col=0N
Is there any better way to find the records which are mismatch in 2 tables?
Hi Vikas
Have you looked at except?
q)trade1:([time:09:00 09:01 09:03]sym:ABC;price:1 2 3) q)trade2:([time:09:00 09:01 09:04 09:05]sym:AAB`C;price:1 2 3 4)
q)(0!trade1) except 0!trade2
time sym price
09:01 B 2
09:03 C 3
q)(0!trade2) except 0!trade1
time sym price
09:01 A 2
09:04 B 3
09:05 C 4
I hope this helps
Derek Piar
Junior Financial Software Developer
AQUAQ Analytics
Thanks Derek. But i think it will not work in my case as i have to use join on few fix columns regardless of other values in another table. it also not working with primary keys table.
Even though i create temp table with join columns only, and then do a except i will not get index but direct values. it will be doable but i think it will take much time.
Thanks Charles for the solution you have provided. I was trying to refactor diffTables for my need as i do not have where condition and sym column. But i am getting Error rank. Even if i use miller.q and try to run following commands it gives same error:
diffTables[trade1;trade2;`a;`time`sym]
lcs[“ABCABA”;“CABBA”;=]
diffTables:{[t1;t2;c]
i1:til count t1
;i2:til count t2
;if[0 = (count i1)&(count i2); :(i1;i2)] /if either entry is empty, return
;a:t1 i1
;b:t2 i2
;if[1<(count c); [a:flip (t1 i1) c;b:flip (t2 i2) c];[a:t1 i1;b:t2 i2]]
/;:all t1=t2 fails in next line
;il:lcs[a;b;{[x;y] all x=y}] /replace with call to lcsopt instead if optimizing for big tables with long common prefix/suffixes
;:il
;dela: (til count a) except il[0] //return delta indices, i.e., not a common subsequence in a
;delb: (til count b) except il[1] //return delta indices, i.e., not a common subsequence in b
;:(i1 dela; i2 delb) //return the delta indices in original table
}
I shall try to debug lcs function whats the issue.
My Bad i was trying to run scripts from QStudio and functions were not having ;(terminate) so it was causing the issue. Thanks again.
Hi,
diffTables was throwing length exception in case of 2 tables were not of same length. I corrected by change in snakes function, Line no 43. Not sure if i am doing something wrong or it required correction.
;condTrue:1b;
while[(x<n) and (y<m) and condTrue;if[f[a;b[y]];condTrue:0b];x+:1;y+:1]; //f is boolean function on a[i],b[j]