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
|