Main | What does it mean for a table to be a flipped dictionary? »

How does fby work?

According to code.kx.com, fby is short for filter-by, and it is commonly used as the q equivalent to SQL's HAVING clause (though, like where, fby is a q function, and its use is not limited to where clauses).

What fby does is aggregate values from one list based on groups defined in another, parallel, list. For example, suppose we have one list of cities and another list with a few temperature samples for each. We can use fby to calculate the minimum temperature sample for each city, and then replicate those values at each position for each corresponding city:

q)city:`NY`NY`LA`SF`LA`SF`NY
q)temp:32 31 75 69 70 68 12
q)(min;temp) fby city
12 12 70 68 70 68 12
q)

Thus, NY's minimum temperature, 12, appears at every index in fby's output where `NY appears in city.

At the core of fby (like by) is group, which organizes the distinct values in a list into a dictionary mapping those values to their indices:

q)city:`NY`NY`LA`SF`LA`SF`NY
q)group city
NY| 0 1 6
LA| 2 4
SF| 3 5
q)

We can group the temperatures for each city together by indexing temp with the value of the grouped city dictionary:

q)grouped: value group city
q)temp[grouped]
32 31 12
75 70
69 68
q)

Note that the result of indexing temp with grouped is a nested list with the same shape as grouped. This is a general principle: the result of an indexing operation has the shape of the index.

Now we can apply an aggregation function to each of the temperature groups:

q)min each temp[grouped]
12 70 68
q)

We're almost there. The real trick of fby is placing each aggregation result into a new list so that each element has the correct value per the grouping list. We can use @ (functional amend) to get the job done (see also the functional apply/amend faq):

q)@[temp; grouped; :; min each temp[grouped] ]
12 12 70 68 70 68 12
q)

The real fby is just slightly more complicated to ensure that the first argument to @ has the correct type.

PrintView Printer Friendly Version

EmailEmail Article to Friend

Reader Comments (2)

I find using a dicitonary lookup (as below) slightly easier. I think this should work for all cases, correct?
q)city:`NY`NY`LA`SF`LA`SF`NY
q)temp:32 31 75 69 70 68 12
q)((min;temp) fby city)~(min each temp group city) city
1b

February 1, 2013 | Unregistered CommenterJose

Why doesn't the below expression work?

@[temp; grouped; :; min each temp[grouped] ]
`type

February 23, 2013 | Unregistered CommenterSudhakar

PostPost a New Comment

Enter your information below to add a new comment.

My response is on my own website »
Author Email (optional):
Author URL (optional):
Post:
 
All HTML will be escaped. Textile formatting is allowed.