emacs-orgmode@gnu.org archives
 help / color / mirror / code / Atom feed
From: Jarmo Hurri <jarmo.hurri@syk.fi>
To: emacs-orgmode@gnu.org
Subject: Small contrib: lookup (was "Preserving the geometry of a table range")
Date: Sun, 02 Sep 2012 17:53:18 +0300	[thread overview]
Message-ID: <87bohozdb5.fsf_-_@syk.fi> (raw)
In-Reply-To: <CALn3zogrJSpNJaNxxO9iywf-veWaXaH0UB+MGxiq3dARBo-8TA@mail.gmail.com>


Michael Brand <michael.ch.brand@gmail.com> 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

      reply	other threads:[~2012-09-02 14:53 UTC|newest]

Thread overview: 6+ messages / expand[flat|nested]  mbox.gz  Atom feed  top
2012-08-26  6:54 Preserving the geometry of a table range Jarmo Hurri
2012-08-26  7:10 ` Michael Brand
2012-08-26  8:45   ` Jarmo Hurri
2012-08-26 18:58     ` Michael Brand
2012-08-27 12:33 ` Michael Brand
2012-09-02 14:53   ` Jarmo Hurri [this message]

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

  Avoid top-posting and favor interleaved quoting:
  https://en.wikipedia.org/wiki/Posting_style#Interleaved_style

  List information: https://www.orgmode.org/

* Reply using the --to, --cc, and --in-reply-to
  switches of git-send-email(1):

  git send-email \
    --in-reply-to=87bohozdb5.fsf_-_@syk.fi \
    --to=jarmo.hurri@syk.fi \
    --cc=emacs-orgmode@gnu.org \
    --subject='Re: Small contrib: lookup (was "Preserving the geometry of a table range")' \
    /path/to/YOUR_REPLY

  https://kernel.org/pub/software/scm/git/docs/git-send-email.html

* If your mail client supports setting the In-Reply-To header
  via mailto: links, try the mailto: link

Code repositories for project(s) associated with this inbox:

	https://git.savannah.gnu.org/cgit/emacs/org-mode.git

This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox;
as well as URLs for read-only IMAP folder(s) and NNTP newsgroup(s).