## 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.

## What does it mean for a table to be a flipped dictionary?

A table is a reference to a (column) dictionary.

The internal representation of a table is nearly identical to that of a dictionary. We can use the flip command to create a table from a dictionary:

```q)t: ([] a: 1 2 3; b: 4 5 6; c: 7 8 9)
q)d: `a`b`c ! (1 2 3; 4 5 6; 7 8 9)
q)t ~ flip d
1b
q)
```

In fact, when a dictionary is `flip`‘ed, the underlying core data structure remains untouched. The table itself is a simple, small object that refers to the original dictionary. Using .Q.w, we can measure how much more memory a table takes than the corresponding dictionary:

```q).Q.w[]`used         // memory usage baseline
112992j
q)x:`a`b`c!3 3#til 9  // create a small dictionary
q).Q.w[]`used
113424j
q)x:flip x
q).Q.w[]`used         // memory usage delta is
113456j               // just 32 more bytes
q)
```

No matter how large the underlying dictionary is, creating a table is fast and still takes only 32 bytes:

```q)x:`a`b`c!3 100000#til 10
q).Q.w[]`used
1686192j
q)x:flip x
q).Q.w[]`used
1686224j          // 32 = 1686224 - 1686192
q)
```

Now, Let’s examine how a keyed table is related to a dictionary. We start by creating a simple keyed table:

```q)t: ([] a: 1 2 3; b: 4 5 6; c: 7 8 9)
q)keyedTable: `a`b xkey t
q)keyedTable
a b| c
---| -
1 4| 7
2 5| 8
3 6| 9
q)keys keyedTable
`a`b
q)
```

Since `keyedTable` is a table, one might expect it to have the same type as t but instead, q presents the following surprise:

```q)type t
98h                  // type table as expected
q)type keyedTable
99h                  // *NOT* 98h
q)
```

Type 99h is the type number for dictionaries. If `keyedTable` really is a dictionary, we should be able to extract its `key` and `value`:

```q)key keyedTable
a b
---
1 4
2 5
3 6
q)value keyedTable
c
-
7
8
9
q)
```

Indeed, `keyedTable` is a dictionary – one that holds unkeyed tables for both its key and its value:

```q)type key keyedTable
98h
q)type value keyedTable
98h
q)
```

This suggests that we can create a keyed table by using the ! (dict) operator with two unkeyed tables:

```q)(key keyedTable)!(value keyedTable)
a b| c
---| -
1 4| 7
2 5| 8
3 6| 9
q)
```

Lastly, joining the two flipped tables brings us back to the original dict.

```q)(flip key keyedTable),(flip value keyedTable)
a| 1 2 3
b| 4 5 6
c| 7 8 9
q)
```