Riddle 4 Answer

So apparently this is an annual series now…

Anyway, Dan Nugent and Akash were both on the right track, but neither had it exactly right: when a qsql query inside a function is executed, relative names used in it resolve against globals in the current namespace, not the namespace that was in effect when the function was created (i.e. the one returned by running {(get x). 3 0} on the function).

(Note that this actually applies to any type of global (e.g. an atom, a vector, etc.) referenced from a query inside a function, but for convenience, I’ll be writing this post assuming a function is what’s being referenced.)

I will admit to making this a bit of a trick question, as the way I constructed the example was designed around the most common case, which is entirely consistent with Dan’s and Akash’s answers. Here's a snippet showing the full behavior:

% q
KDB+ 3.3 2015.09.02 Copyright (C) 1993-2015 Kx Systems
m32/ 16()core 8192MB adavies aaron-daviess-mac-pro.local 192.168.1.151 NONEXPIRE  

q)f:{x+1}
q)\d .foo
q.foo)g:{select f a from x}
q.foo)\d .bar
q.bar).foo.g([]a:1 2 3)
{select f a from x}
'f
q.foo))\
q.bar)f:{x+2}
q.bar).foo.g([]a:1 2 3)
a
-
3
4
5
q.bar)

Compare to this snippet, where I reference the function from outside the qsql query:

% q
KDB+ 3.3 2015.09.02 Copyright (C) 1993-2015 Kx Systems
m32/ 16()core 8192MB adavies aaron-daviess-mac-pro.local 192.168.1.151 NONEXPIRE  

q)\d .foo
q.foo)f:{x+1}
q.foo)g:{f select a from x}
q.foo)\d .
q).foo.g([]a:1 2 3)
a
-
2
3
4
q)

This can become problematic if you try to create a group of functions in a namespace, some of which reference each other in queries, and then call those functions from outside that namespace. I’ve found two solutions for this, both unfortunately rather inelegant.

1. You can “copy” the function you need from the global space to a local variable by referencing it from outside any qsql queries:

   % q
   KDB+ 3.3 2015.09.02 Copyright (C) 1993-2015 Kx Systems
   m32/ 16()core 8192MB adavies aaron-daviess-mac-pro.local 192.168.1.151 NONEXPIRE  
   
   q)\d .foo
   q.foo)f:{x+1}
   q.foo)g:{f0:f;select f0 a from x}
   q.foo)\d .
   q).foo.g([]a:1 2 3)
   a
   -
   2
   3
   4
   q)

2. You can do the name resolution yourself, by leveraging the get results to automatically reference the correct namespace:

   % q
   KDB+ 3.3 2015.09.02 Copyright (C) 1993-2015 Kx Systems
   m32/ 16()core 8192MB adavies aaron-daviess-mac-pro.local 192.168.1.151 NONEXPIRE  
   
   q)\d .foo
   q.foo)f:{x+1}
   q.foo)g:{select((` sv`,((get .z.s). 3 0))`f)a from x}
   q.foo)\d .
   q).foo.g([]a:1 2 3)
   a
   -
   2
   3
   4
   q)

   This can be encapsulated in a utility function, but note that it must then itself be referenced by an absolute name, or the same problem will apply to it:
   

   % q
   KDB+ 3.3 2015.09.02 Copyright (C) 1993-2015 Kx Systems
   m32/ 16()core 8192MB adavies aaron-daviess-mac-pro.local 192.168.1.151 NONEXPIRE  
   
   q)\d .util
   q.util)r:{(` sv`,((get x). 3 0))y}
   q.util)\d .foo
   q.foo)f:{x+1}
   q.foo)g:{select((.util.r .z.s)`f)a from x}
   q.foo)\d .
   q).foo.g([]a:1 2 3)
   a
   -
   2
   3
   4
   q)

Riddle 3 Answer

And it looks like I did it again—Riddle 3 has been lacking an official answer for almost a year!
Ciaran Gorman’s answer was correct—strings with leading and/or trailing space are exactly what I was thinking of, and the serialization technique he showed is how to deal with them.
The following function should work as a general solution:

{0x01,($[.z.o like"s*";reverse;::]0x0 vs"i"$10+count x),0x000000f5,("x"$x),0x00}

Name! That! Function! (Pivot Table Edition)

What time is it, kids? That’s right, it’s time to play Name! That! Function!
Seriously though, I have a small, useful (IMAO) function I’ve been entering freehand in the console for something like two years now.
Normally, I’d put it in my personal library, but there’s a problem—I can’t think of a good name for it.
Here’s the function: {((union)over key each x)#/:x}.
And here it is in context, showing what it’s good for:

q)t:([]id:1 1 2 2 3;k:`a`b`b`a`b;v:1 2 3 4 5)
q)t
id k v
------
1  a 1
1  b 2
2  b 3
2  a 4
3  b 5
q)exec k!v by id:id from t
id|         
--| --------
1 | `a`b!1 2
2 | `b`a!3 4
3 | (,`b)!,5
q){((union)over key each x)#/:x}exec k!v by id:id from t
id| a b
--| ---
1 | 1 2
2 | 4 3
3 |   5
q)

So, there it is—an easy way to fix up ad-hoc pivots¹ when your data doesn’t have all keys present (and in the same order) on all ids.
Anyone have any ideas what to call it?

1. Note that for production pivots, particularly if they involve significant amounts of data, you should be using an optimized pivot function.↩

Quick Tip: Intra-Statement Breakpoints

A statement referencing a non-existent variable is a common way to add a breakpoint to a q function.

q)f:{x:x+1;break;x+2}

While this is handy, it only lets you break between statements. A simple extension lets you break within statements, inspecting values at arbitrary points in the code, and continuing with execution once you’re done:

q)f:{x:x+1;{break;x}x+2}

Now, the break will occur after the portion of the statement to the right of the break function has executed, and x within the break function will have the value returned by that code. Since the break statement itself has no effects, and x contains the value returned by the code executed so far, typing : to continue will allow the rest of the break function to execute, returning x leftwards and allowing the rest of the statement to execute as if nothing had interrupted it.

Note that this is entirely legal inside qsql queries; I’ve often found it of particular utility there, since you can’t create new local variables inside a query. Unfortunately no specific examples come to mind at the moment; I’ll try to post one later to make it clearer how this technique works.

Quick Tip: Statistics on Booleans

Due to q’s type promotion rules, it’s entirely legal to use statistical functions on boolean vectors. avg tends to be the most useful, but all of them should work as expected.

A typical use case: average nullness (handy while developing ETL code). We will simulate with a vector of floats mistakenly parsed as ints (due to mostly looking like ints):

q)v:@[string 1000?1000;-10?1000;,[;".123"]]
q)t:flip(enlist`v)!(enlist"I";" ")0:v
q)t
v  
---
468
959
221
694
934
865
344
997
314
580
45 
745
898
935
64 
177
238
361
850
241
..
q)avg null t
v| 0.01
q)

So 10% of t.v is null, which (unless this is expected) should cause us to ask ourselves whether "I" was really the right parse.