From mboxrd@z Thu Jan 1 00:00:00 1970 From: Thierry Banel Subject: Re: TBLFM and a remote table lookup on two columns Date: Fri, 22 Jun 2018 08:33:03 +0200 Message-ID: <0f78f8fd-c579-6733-f63e-c5dbf04f3fa3@free.fr> References: <1631286440.844682.1529646045252.ref@mail.yahoo.com> <1631286440.844682.1529646045252@mail.yahoo.com> Mime-Version: 1.0 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable Return-path: Received: from eggs.gnu.org ([2001:4830:134:3::10]:58218) by lists.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1fWFd8-0001e7-CH for emacs-orgmode@gnu.org; Fri, 22 Jun 2018 02:33:11 -0400 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1fWFd4-0004WV-6G for emacs-orgmode@gnu.org; Fri, 22 Jun 2018 02:33:10 -0400 Received: from smtp1-g21.free.fr ([212.27.42.1]:51446) by eggs.gnu.org with esmtps (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:32) (Exim 4.71) (envelope-from ) id 1fWFd3-0004W5-T9 for emacs-orgmode@gnu.org; Fri, 22 Jun 2018 02:33:06 -0400 Received: from [IPv6:2a01:e35:2e21:def0:61ac:ee16:ceba:ad6c] (unknown [IPv6:2a01:e35:2e21:def0:61ac:ee16:ceba:ad6c]) by smtp1-g21.free.fr (Postfix) with ESMTP id 09DE4B0056B for ; Fri, 22 Jun 2018 08:33:03 +0200 (CEST) In-Reply-To: <1631286440.844682.1529646045252@mail.yahoo.com> Content-Language: en-US List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org Sender: "Emacs-orgmode" To: emacs-orgmode@gnu.org

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.=C2=A0 It works great but I've= run into something that has stumped me.=C2=A0 Imagine I have a table like the following:

#+name: lookup_geo

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

| T=C2=A0 =C2=A0 =C2=A0 | L=C2=A0 =C2=A0| Y |

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

| WEII=C2=A0 | SEA | 3 |

| WEIII | SEA | 4 |

| WEIII | SJC | 3 |

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

|--------+-----+-------+----------------------------= -----|

| T=C2=A0 =C2=A0 =C2=A0 | L=C2=A0 =C2=A0| Count | Co= st (Count * matching Y above) |

|--------+-----+-------+----------------------------= -----|

| WEIII | SJC |=C2=A0 =C2=A0 =C2=A03 | #ERROR=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |

| WEIII | SEA |=C2=A0 =C2=A0 =C2=A05 | #ERROR=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |

|--------+-----+-------+----------------------------= -----|

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

#+TBLFM: $4=3D`(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.=C2=A0 While I could workaround t= his 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:

=C2=A0 =C2=A0 'lambda (row) (and (=3D $1 (car row)) (=3D= $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=C2=A0=C2=A0=C2=A0=C2=A0 | L=C2=A0=C2=A0 |=C2=A0 Y | |-------+-----+----| | WEII=C2=A0 | SEA | 11 | | WEII=C2=A0 | SEA | 22 | | WEII=C2=A0 | SJC | 33 | | WEIII | SEA |=C2=A0 4 | | WEIII | SJC |=C2=A0 3 | | WEIII | SJC | 50 | | WEII=C2=A0 | SEA | 44 | |-------+-----+----| #+BEGIN: aggregate :table "lookup_geo" :cols "T L count() sum(Y)" | T=C2=A0=C2=A0=C2=A0=C2=A0 | L=C2=A0=C2=A0 | count() | su= m(Y) | |-------+-----+---------+--------| | WEII=C2=A0 | SEA |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 3= |=C2=A0=C2=A0=C2=A0=C2=A0 77 | | WEII=C2=A0 | SJC |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 1= |=C2=A0=C2=A0=C2=A0=C2=A0 33 | | WEIII | SEA |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 1 |=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0 4 | | WEIII | SJC |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 2 |=C2= =A0=C2=A0=C2=A0=C2=A0 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.milkb= ox.net/packages/") t) (package-initialize) Have fun Thierry