Hi q-gods,Currently I have encountered a problem to implement wildcard matchingin tables.Suppose I have two tables, a and b, as follow:acond1 cond21 12 0Nbcond1 cond2 value1 1 101 2 362 1 212 2 17Result will be rows in table b that meet conditions in table a bytreating null as a wildcard.In the above example, the result will beresultcond1 cond2 value1 1 102 1 212 2 17I would like to know is it possible to achieve it without using each,i.e. via table-based operationCurrently what I can think of is the use of union of “inter” between aand b using the power set of cond, i.e.compare a to bcompare a to (update cond1: 0N from b)compare a to (update cond2: 0N from b)compare a to (update cond1: 0N, cond2: 0N from b)but that’s kind of slow operation and not really good to extend tomultiple conditions (definitely slower than each operation).That’s why I would like q-gods to hint me how to solve it efficientlyThanks q-gods in advance :)Charles
hi,
hope the performance will be sufficient;
cmp:{x where (|/)flip[x z]{$[null y 1;x[0]=y 0;(&/)x=y]}:/:flip y z}
cmp[b;a; cond1cond2]
patryk
On Apr 24, 2012 11:58 AM, “Charles Lin” <charles1117@gmail.com> wrote:
Hi Patryk,
Thanks really much for your reply and the fact that it works for the above example
I have dug into your code, and am wondering the following (correct me if I am wrong):
- It seems not catering the situation where cond1 can also be a wildcard (Judging from null y 1), for example
a
cond1 cond2
1 1
2 0N
0N 3
b
cond1 cond2 value
1 1 10
1 2 36
2 1 21
2 2 17
7 3 42
If I would like to obtain the result
result
cond1 cond2 value
1 1 10
2 1 21
2 2 17
7 3 42
Using your function I need to do something like cmp[b; a; cond1cond2] union cmp[b; a; cond2cond1], right?
Then it seems equivalent to what I have mentioned in the first post – traversing through the power set of conditions
2. The function is not really extensible if I want to generalize the problem and take 3 or 4 parameters, every single one is wildcard replacable (Judging from the 0 and 1 in the inner function)
3.The use of adverb /: and :, they are not really good for kdb+ evaluation since it is kind of each statement (row-based operation instead of table-based operation)
Hope you can answer my questions above.
Thanks really much in advance.
Charles
On Tuesday, April 24, 2012 8:14:06 PM UTC+8, P.Buko...@gmail.com wrote:
hi,
hope the performance will be sufficient;cmp:{x where (|/)flip[x z]{$[null y 1;x[0]=y 0;(&/)x=y]}:/:flip y z}
cmp[b;a;
cond1cond2]patryk
On Apr 24, 2012 11:58 AM, “Charles Lin” <charles1117@gmail.com> wrote:
Hi Charles,
You’re right.
This will allow you to pass more columns, all can have wildcards:
cmp:{x where (|/)flip[x z]{(&/)null[y]|x=y]}:/:flip y z}
It can be run the same way.
I’ll have to think how to get rid of this cross.
It’s not so bad for small ‘a’…
cheers
patryk
ok, I have more vectorised one
cmp:{x where (|/)(x z){(&/)null[y]|y=x]}/:flip y z}
It’s column set by condition check, I’m close to a conclusion that it’s one if proper solutions, should also perform well.
cheers
patryk
Hi Patryk,
Sorry that I have been out of town the past few days and missed your reply
Thanks for the solution, I am really delighted to see the solution in that
- It is a general solution applicable to not just integer, but all the other types
- It can be extended to support wildcard for more columns
- It is a column-based check, which should be quick fast comparing to atom by atom comparison
Allow me to correct a typo in your last solution. The function should be getting rid of the close bracket after y = x, i.e.
cmp:{x where (|/)(x z){(&/)null[y]|y=x}/:flip y z}
Again, thanks for your solution and hope it will also come in handy for other q programmers who have similar problem :)
All the best,
Charles