From mboxrd@z Thu Jan 1 00:00:00 1970 From: Brad Knotwell Subject: TBLFM and a remote table lookup on two columns Date: Fri, 22 Jun 2018 05:40:45 +0000 (UTC) Message-ID: <1631286440.844682.1529646045252@mail.yahoo.com> References: <1631286440.844682.1529646045252.ref@mail.yahoo.com> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_844681_905939002.1529646045249" Return-path: Received: from eggs.gnu.org ([2001:4830:134:3::10]:51308) by lists.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1fWEoZ-0000It-Hd for emacs-orgmode@gnu.org; Fri, 22 Jun 2018 01:40:57 -0400 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1fWEoU-0006zR-Kj for emacs-orgmode@gnu.org; Fri, 22 Jun 2018 01:40:55 -0400 Received: from sonic302-20.consmr.mail.ne1.yahoo.com ([66.163.186.146]:43619) by eggs.gnu.org with esmtps (TLS1.0:RSA_AES_128_CBC_SHA1:16) (Exim 4.71) (envelope-from ) id 1fWEoU-0006z4-Dw for emacs-orgmode@gnu.org; Fri, 22 Jun 2018 01:40:50 -0400 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 ------=_Part_844681_905939002.1529646045249 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Good day all-- I've been using TBLFM and orgmode-lookup-first to conditionally update a ta= ble.=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 || WE= III | SJC | 3 ||--------+-----+---| |--------+-----+-------+---------------------------------|| T=C2=A0 =C2=A0 = =C2=A0 | L=C2=A0 =C2=A0| Count | Cost (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 Whil= e I could workaround this with multiple tables (e.g. a separate named table= /location and then have code dynamically generate the appropriate table nam= e), a separate source block or a single key composed of two parts (e.g "WEI= I - SEA"; this is my current favorite workaround), I wondered if there migh= t 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 wor= k: =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 th= at combines what was two columns? Thx. --Brad =20 =20 ------=_Part_844681_905939002.1529646045249 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Good day all--

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

#+name: lookup_geo
--------+-----+---|
| T      | L&= nbsp;  | Y |
|--------+-----+---|
| WEII  | S= EA | 3 |
| WEIII | SEA | 4 |
| WEIII | SJC | 3 |
<= div>|--------+-----+---|

|--------+-----+-------+-= --------------------------------|
| T      | L&nbs= p;  | Count | Cost (Count * matching Y above) |
|--------+--= ---+-------+---------------------------------|
| WEIII | SJC |&nb= sp;    3 | #ERROR             =             |
| WEIII | SEA | = ;    5 | #ERROR              &= nbsp;           |
|--------+-----+------= -+---------------------------------|

# the TBLFM s= yntax below doesn't work, it's just how I would've guessed it might.
<= div>
#+TBLFM: $4=3D`(org-lookup-first '($1 $2) '((= remote(lookup_geo,@2$1..@>$1)) (remote(lookup_geo,@2$2..@>$2))) '(rem= ote(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 bot= h.  While I could workaround this with multiple tables (e.g. a separat= e named table/location and then have code dynamically generate the appropri= ate table name), a separate source block or a single key composed of two pa= rts (e.g "WEII - SEA"; this is my current favorite workaround), I wondered = if there might be a better solution.

I also consid= ered whether it would make sense for the predicate to be given access to th= e entire matched row so something like the following could work:
=
    '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
=20
=20

------=_Part_844681_905939002.1529646045249--