Re: [O] TBLFM and a remote table lookup on two columns

[Thierry Banel]

On 22/06/2018 07:40, Brad Knotwell wrote:

Good day all--

I've been using TBLFM and orgmode-lookup-first to conditionally update a table.  It works great but I've run into something that has stumped me.  Imagine I have a table like the following:

#+name: lookup_geo

--------+-----+---|

| T      | L   | Y |

|--------+-----+---|

| WEII  | SEA | 3 |

| WEIII | SEA | 4 |

| WEIII | SJC | 3 |

|--------+-----+---|

|--------+-----+-------+---------------------------------|

| T      | L   | Count | Cost (Count * matching Y above) |

|--------+-----+-------+---------------------------------|

| WEIII | SJC |     3 | #ERROR                          |

| WEIII | SEA |     5 | #ERROR                          |

|--------+-----+-------+---------------------------------|

# the TBLFM syntax below doesn't work, it's just how I would've guessed it might.

#+TBLFM: $4=`(org-lookup-first '($1 $2) '((remote(lookup_geo,@2$1..@>$1)) (remote(lookup_geo,@2$2..@>$2))) '(remote(lookup_geo,@2$3..@>$3))))

As far as I can tell, orgmode-lookup-first allows me to match the first(T) or second(L) column but it's entirely unclear how to match both.  While I could workaround this with multiple tables (e.g. a separate named table/location and then have code dynamically generate the appropriate table name), a separate source block or a single key composed of two parts (e.g "WEII - SEA"; this is my current favorite workaround), I wondered if there might be a better solution.

I also considered whether it would make sense for the predicate to be given access to the entire matched row so something like the following could work:

    'lambda (row) (and (= $1 (car row)) (= $2 (cadr row))))

Anyhow, is there a better way to do this beyond creating a single column that combines what was two columns?

Thx.

--Brad

You may want to take a look at the orgtbl-aggregate package available on Melpa. Suppose you have several combinations of first and second columns values, and you want to aggregate rows based on those combined values. Then orgtbl-aggregate can do the job. In this example it performs two aggregations: count and sum of Y column values:

#+name: lookup_geo
|-------+-----+----|
| T     | L   |  Y |
|-------+-----+----|
| WEII  | SEA | 11 |
| WEII  | SEA | 22 |
| WEII  | SJC | 33 |
| WEIII | SEA |  4 |
| WEIII | SJC |  3 |
| WEIII | SJC | 50 |
| WEII  | SEA | 44 |
|-------+-----+----|

#+BEGIN: aggregate :table "lookup_geo" :cols "T L count() sum(Y)"
| T     | L   | count() | sum(Y) |
|-------+-----+---------+--------|
| WEII  | SEA |       3 |     77 |
| WEII  | SJC |       1 |     33 |
| WEIII | SEA |       1 |      4 |
| WEIII | SJC |       2 |     53 |
#+END:

Documentation here: https://github.com/tbanel/orgaggregate

To gain access to the Melpa repository I have those settings in my .emacs file:

(require 'package)
(add-to-list 'package-archives '("melpa" . "http://melpa.milkbox.net/packages/") t)
(package-initialize)

Have fun
Thierry