[PATCH] Lookup functions for tables

[Jarmo Hurri <jarmo.hurri@syk.fi>]

* lisp/org-table.el: added functions org-lookup-first and
org-lookup-last
* doc/org.texi: documented the use of lookup functions

TINYCHANGE
---
 doc/org.texi      |   88 +++++++++++++++++++++++++++++++++++++++++++++++++++-
 lisp/org-table.el |   10 ++++++
 2 files changed, 96 insertions(+), 2 deletions(-)

diff --git a/doc/org.texi b/doc/org.texi
index e183969..fc7d9dd 100644
--- a/doc/org.texi
+++ b/doc/org.texi
@@ -2399,6 +2399,7 @@ formula, moving these references by arrow keys
 * Formula syntax for Lisp::     Writing formulas in Emacs Lisp
 * Durations and time values::   How to compute durations and time values
 * Field and range formulas::    Formula for specific (ranges of) fields
+* Lookup functions::            Lookup functions for searching tables
 * Column formulas::             Formulas valid for an entire column
 * Editing and debugging formulas::  Fixing formulas
 * Updating the table::          Recomputing all dependent fields
@@ -2729,7 +2730,7 @@ example above).
 Negative duration values can be manipulated as well, and integers will be
 considered as seconds in addition and subtraction.
 
-@node Field and range formulas, Column formulas, Durations and time values, The spreadsheet
+@node Field and range formulas, Lookup functions, Durations and time values, The spreadsheet
 @subsection Field and range formulas
 @cindex field formula
 @cindex range formula
@@ -2785,7 +2786,90 @@ can also be used to assign a formula to some but not all fields in a row.
 Named field, see @ref{Advanced features}.
 @end table
 
-@node Column formulas, Editing and debugging formulas, Field and range formulas, The spreadsheet
+@node Lookup functions, Column formulas, Field and range formulas, The spreadsheet
+@subsection Lookup functions
+@cindex lookup functions in tables
+@cindex table lookup functions
+
+Org has two predefined Emacs Lisp functions for lookups in tables.
+@table @code
+@item (org-lookup-first val search-list return-list &optional predicate)
+@findex org-lookup-first
+Searches for the first element @code{el} in list @code{search-list} for which
+@lisp
+(predicate val el)
+@end lisp
+is @code{t}; returns a value from the corresponding
+position in list @code{return-list}. The default @code{predicate} is
+@code{equal}.
+@item (org-lookup-last val search-list return-list &optional predicate)
+@findex org-lookup-last
+Similar as @code{org-lookup-first} above, but searches for the @i{last} element for which the predicate is
+@code{t}.
+@end table
+
+The examples below illustrate searches inside a single table. In real-world
+applications, the searched data is often in a different table and is accessed
+by remote references.
+
+The first example contains the searched data in the first and the second
+column. The lookup is performed in column 5, where the year corresponding to
+the percentage in column 4 is searched. Notice that an error is returned if
+the lookup is unsuccessful.
+@example
+@group
+  | year | percentage |   | percentage | year   |
+  |------+------------+---+------------+--------|
+  | 2009 |       12.2 |   |       14.3 | 2010   |
+  | 2010 |       14.3 |   |       19.4 | 2012   |
+  | 2011 |       14.3 |   |       11.5 | #ERROR |
+  | 2012 |       19.4 |   |            | #ERROR |
+  #+TBLFM: $5='(org-lookup-first $4 '(@@2$2..@@>$2) '(@@2$1..@@>$1));N
+@end group
+@end example
+
+The second example illustrates standard use of lookups for teachers. The
+first two columns contain a grading table. The fourth and the fifth column
+contain student names and their marks, and the last column contains the
+results of doing a lookup for the appropriate grade. Notice the use of
+@code{org-lookup-last}, the predicate @code{>=} and the use of the @code{L}
+flag for literal interpolation of table values.
+@example
+@group
+  | lower bound | grade |   | student | marks | grade |
+  |-------------+-------+---+---------+-------+-------|
+  |           0 | D     |   | X       |    33 | A     |
+  |          10 | C     |   | Y       |     5 | D     |
+  |          20 | B     |   | Z       |    10 | C     |
+  |          30 | A     |   | W       |    22 | B     |
+  #+TBLFM: $6='(org-lookup-last $5 '(@@2$1..@@>$1) '(@@2$2..@@>$2) '>=);L
+@end group
+@end example
+
+In the previous examples the searched ranges were one-dimensional (single
+columns). Because two-dimensional ranges are converted to one-dimensional
+vectors in Lisp expressions, it is also possible to search true
+two-dimensional ranges. The example below does a lookup in the two first
+columns for values whose distance from @code{search key} is at most 1.
+@example
+@group
+#+BEGIN_SRC emacs-lisp
+(defun my-p (val1 val2)
+  (<= (abs (- val1 val2)) 1))
+#+END_SRC
+
+#+RESULTS:
+: my-p
+
+| group 1 | group 2 |   | search key | result |
+|---------+---------+---+------------+--------|
+|      22 |      12 |   |         -9 |     -8 |
+|      -8 |      11 |   |         23 |     22 |
+#+TBLFM: $5='(org-lookup-first $4 '(@@2$1..@@>$2) '(@@2$1..@@>$2) 'my-p);N
+@end group
+@end example
+
+@node Column formulas, Editing and debugging formulas, Lookup functions, The spreadsheet
 @subsection Column formulas
 @cindex column formula
 @cindex formula, for table column
diff --git a/lisp/org-table.el b/lisp/org-table.el
index 37889af..174fe59 100644
--- a/lisp/org-table.el
+++ b/lisp/org-table.el
@@ -4826,6 +4826,16 @@ list of the fields in the rectangle ."
 		      (org-table-get-range (match-string 0 form) tbeg 1))
 		  form)))))))))
 
+(defmacro org-lookup-function (name-str from-end-p)
+  `(defun ,(intern (format "org-lookup-%s" name-str)) (val search-list return-list &optional predicate)
+     "Searches for the element el in list search-list for which
+(predicate val el) is t; returns a value from the corresponding
+position in list return-list. The default predicate is equal."
+     (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)
+
 (provide 'org-table)
 
 ;;; org-table.el ends here
-- 
1.7.7.6