From mboxrd@z Thu Jan 1 00:00:00 1970 From: Jarmo Hurri Subject: Small contrib: lookup (was "Preserving the geometry of a table range") Date: Sun, 02 Sep 2012 17:53:18 +0300 Message-ID: <87bohozdb5.fsf_-_@syk.fi> References: <87lih25ejt.fsf@syk.fi> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Return-path: Received: from eggs.gnu.org ([208.118.235.92]:56517) by lists.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1T8BYJ-0007jj-Af for emacs-orgmode@gnu.org; Sun, 02 Sep 2012 10:53:32 -0400 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1T8BYI-0007uo-5M for emacs-orgmode@gnu.org; Sun, 02 Sep 2012 10:53:31 -0400 Received: from plane.gmane.org ([80.91.229.3]:60872) by eggs.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1T8BYH-0007uj-UJ for emacs-orgmode@gnu.org; Sun, 02 Sep 2012 10:53:30 -0400 Received: from list by plane.gmane.org with local (Exim 4.69) (envelope-from ) id 1T8BYH-0002GV-OH for emacs-orgmode@gnu.org; Sun, 02 Sep 2012 16:53:29 +0200 Received: from cs181237063.pp.htv.fi ([82.181.237.63]) by main.gmane.org with esmtp (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Sun, 02 Sep 2012 16:53:29 +0200 Received: from jarmo.hurri by cs181237063.pp.htv.fi with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Sun, 02 Sep 2012 16:53:29 +0200 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-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org To: emacs-orgmode@gnu.org Michael Brand writes: > Does this help? Yes indeed! It gave me both a better idea of what the call should look like and the way to implement it. The only problem with this approach - using a separate search and return list - when compared with the lookup functions of traditional spreadsheets, is that you need to specify the range twice. But then again in this form the range can be even from different tables (although that is not very likely to happen). How do you guys find the definitions and the examples at the end of this message? I decided to write a macro and use it to define two functions rather than having one function with an optional argument. Made me learn how to use elisp macros, and also makes it very explicit for the user whether he/she is searching for the first or the last match. The last example demonstrates that you can do lookups in general range geometries of type (n x m). Could we have a form of remote which takes just one argument and then refers to the whole table? (See the last example.) Can we also have multidimensional tables? :-) * macro for defining two lookup functions org-lookup-first and org-lookup-last #+BEGIN_SRC emacs-lisp (defmacro org-lookup-function (name-str from-end-p) `(defun ,(intern (format "org-lookup-%s" name-str)) (val search-list return-list &optional predicate) (let ((p (if (eq predicate nil) 'equal predicate))) (nth (position val search-list :test p :from-end ,from-end-p) return-list)))) (org-lookup-function "first" nil) (org-lookup-function "last" t) #+END_SRC #+RESULTS: : org-lookup-last * example 1: exact matches with default predicate equal data #+TBLNAME: growth-rates | year | percentage | |------+------------| | 2009 | 12.2 | | 2010 | 14.3 | | 2011 | 14.3 | | 2012 | 19.4 | lookups | percentage | year | |------------+------| | 14.3 | 2010 | | 14.3 | 2011 | #+TBLFM: @3$1=@2$1::@2$2='(org-lookup-first @2$1 '(remote(growth-rates,@2$2..@>$2)) '(remote(growth-rates,@2$1..@>$1)));N::@3$2='(org-lookup-last @3$1 '(remote(growth-rates,@2$2..@>$2)) '(remote(growth-rates,@2$1..@>$1)));N * example 2: grades with optional predicate data #+TBLNAME: grade-boundaries | lower bound | grade | |-------------+-------| | 0 | 4 | | 2 | 4.25 | | 4 | 4.5 | | 6 | 4.75 | lookups | student | marks | grade | |---------+-------+-------| | A | 3 | 4.25 | | B | 4 | 4.5 | #+TBLFM: $3='(org-lookup-last $2 '(remote(grade-boundaries,@2$1..@>$1)) '(remote(grade-boundaries,@2$2..@>$2)) '>=);N * example 3: searching a true two-dimensional range data table 1 #+TBLNAME: scores | 3 | 13 | | 4 | 11 | data table 2 #+TBLNAME: groups | X | Y | | Z | W | lookup | score | group | |-------+-------| | 13 | Y | | 4 | Z | #+TBLFM: $2='(org-lookup-first $1 '(remote(scores,@<$<..@>$>)) '(remote(groups,@<$<..@>$>)));L