From: Michael Brand <michael.ch.brand@gmail.com>
To: Org Mode <emacs-orgmode@gnu.org>
Subject: Re: sum up variables from different org-mode tables
Date: Sat, 4 Jan 2014 16:29:36 +0100 [thread overview]
Message-ID: <CALn3zoi0RAGZogUrfJuSpiTm_0r52sZxX8JzDjJzmr6oGDijJQ@mail.gmail.com> (raw)
In-Reply-To: <CALn3zoi4oyakLiKNshvqzmO8os6LySEaSPPn2Rg8x2L3Rrr80g@mail.gmail.com>
[-- Attachment #1: Type: text/plain, Size: 1062 bytes --]
Hi all
On Fri, Nov 16, 2012 at 4:16 PM, Michael Brand
<michael.ch.brand@gmail.com> wrote:
> [...]
> A nice solution for variant 2 would be if
> @2$3..@2$7 = remote(A, @>>$$#) :: @3$3..@3$7 = remote(B, @>>$$#)
> could be simplified to
> @2$3..@3$7 = remote($8, @>>$$#)
> [...]
Because I need the above indirection of remote references I
implemented it a few days ago, see the attached patches. Comments are
welcome, otherwise I will push the changes to the Org repo in a few
days.
This is the use case that is checked in the Emacs Regression Test
test-org-table/remote-reference-indirect in
testing/lisp/test-org-table.el:
#+NAME: 2012
| amount |
|--------|
| 1 |
| 2 |
|--------|
| 3 |
#+TBLFM: @>$1 = vsum(@I..@II)
#+NAME: 2013
| amount |
|--------|
| 4 |
| 8 |
|--------|
| 12 |
#+TBLFM: @>$1 = vsum(@I..@II)
#+NAME: summary
| year | amount |
|-------+--------|
| 2012 | 3 |
| 2013 | 12 |
|-------+--------|
| total | 15 |
#+TBLFM: @<<$2..@>>$2 = remote($<, @>$1) :: @>$2 = vsum(@I..@II)
Michael
[-- Attachment #2: 0001-TBLFM-remote-ref-Add-ERT-for-summary-table.patch.txt --]
[-- Type: text/plain, Size: 2979 bytes --]
From 21152c2045345135fd18749e016367aa0388ae4b Mon Sep 17 00:00:00 2001
From: Michael Brand <michael.ch.brand@gmail.com>
Date: Sat, 4 Jan 2014 16:21:28 +0100
Subject: [PATCH 1/2] TBLFM remote ref: Add ERT for summary table
* testing/lisp/test-org-table.el
(test-org-table/remote-reference-indirect): Add a use case of
summarizing two tables with a sum into one table for the total; as a
preparation for remote reference indirection.
---
testing/lisp/test-org-table.el | 80 ++++++++++++++++++++++++++++++++++++++++++
1 file changed, 80 insertions(+)
diff --git a/testing/lisp/test-org-table.el b/testing/lisp/test-org-table.el
index edb51c4..a4f8caa 100644
--- a/testing/lisp/test-org-table.el
+++ b/testing/lisp/test-org-table.el
@@ -793,6 +793,86 @@ See also `test-org-table/copy-field'."
;; Do a calculation: Use Calc (or Lisp ) formula
"$2 = 2 * remote(table, @1$2)")))
+(ert-deftest test-org-table/remote-reference-indirect ()
+ "Access to remote reference with indirection of name or ID."
+ (let ((source-tables "
+#+NAME: 2012
+| amount |
+|--------|
+| 1 |
+| 2 |
+|--------|
+| 3 |
+#+TBLFM: @>$1 = vsum(@I..@II)
+
+#+NAME: 2013
+| amount |
+|--------|
+| 4 |
+| 8 |
+|--------|
+| 12 |
+#+TBLFM: @>$1 = vsum(@I..@II)
+"))
+
+ ;; Read several remote references from same column
+ (org-test-table-target-expect
+ (concat source-tables "
+#+NAME: summary
+| year | amount |
+|-------+---------|
+| 2012 | replace |
+| 2013 | replace |
+|-------+---------|
+| total | replace |
+")
+ (concat source-tables "
+#+NAME: summary
+| year | amount |
+|-------+--------|
+| 2012 | 3 |
+| 2013 | 12 |
+|-------+--------|
+| total | 15 |
+")
+ 1
+ ;; Calc formula
+ (concat "#+TBLFM: "
+ "@2$2 = remote(2012, @>$1) :: "
+ "@3$2 = remote(2013, @>$1) :: "
+ "@>$2 = vsum(@I..@II)")
+ ;; Lisp formula
+ (concat "#+TBLFM: "
+ "@2$2 = '(identity remote(2012, @>$1)); N :: "
+ "@3$2 = '(identity remote(2013, @>$1)); N :: "
+ "@>$2 = '(+ @I..@II); N"))
+
+ ;; Read several remote references from same row
+ (org-test-table-target-expect
+ (concat source-tables "
+#+NAME: summary
+| year | 2012 | 2013 | total |
+|--------+---------+---------+---------|
+| amount | replace | replace | replace |
+")
+ (concat source-tables "
+#+NAME: summary
+| year | 2012 | 2013 | total |
+|--------+------+------+-------|
+| amount | 3 | 12 | 15 |
+")
+ 1
+ ;; Calc formula
+ (concat "#+TBLFM: "
+ "@2$2 = remote(2012, @>$1) :: "
+ "@2$3 = remote(2013, @>$1) :: "
+ "@2$> = vsum($<<..$>>)")
+ ;; Lisp formula
+ (concat "#+TBLFM: "
+ "@2$2 = '(identity remote(2012, @>$1)); N :: "
+ "@2$3 = '(identity remote(2013, @>$1)); N :: "
+ "@2$> = '(+ $<<..$>>); N"))))
+
(ert-deftest test-org-table/org-at-TBLFM-p ()
(org-test-with-temp-text-in-file
"
--
1.7.12.4 (Apple Git-37)
[-- Attachment #3: 0002-TBLFM-remote-ref-Add-indirection-of-name-or-ID.patch.txt --]
[-- Type: text/plain, Size: 4651 bytes --]
From 38ad5df8ab16713f3e32d1c2106f2b1cf1e6ea42 Mon Sep 17 00:00:00 2001
From: Michael Brand <michael.ch.brand@gmail.com>
Date: Sat, 4 Jan 2014 16:25:42 +0100
Subject: [PATCH 2/2] TBLFM remote ref: Add indirection of name or ID
* doc/org.texi (References): Add description for indirection of
NAME-OR-ID.
* lisp/org-table.el (org-table-eval-formula): Make use of
`org-table-remote-reference-indirection'.
(org-table-remote-reference-indirection): New function.
* testing/lisp/test-org-table.el
(test-org-table/remote-reference-indirect): Change to use remote
reference indirection.
---
doc/org.texi | 6 ++++++
lisp/org-table.el | 25 +++++++++++++++++++++++++
testing/lisp/test-org-table.el | 18 ++++--------------
3 files changed, 35 insertions(+), 14 deletions(-)
diff --git a/doc/org.texi b/doc/org.texi
index bf5dafd..f710971 100644
--- a/doc/org.texi
+++ b/doc/org.texi
@@ -2630,6 +2630,12 @@ table in that entry. REF is an absolute field or range reference as
described above for example @code{@@3$3} or @code{$somename}, valid in the
referenced table.
+Indirection of NAME-OR-ID: When NAME-OR-ID has the format @code{@@ROW$COLUMN}
+it will be substituted with the name or ID found in this field of the current
+table. For example @code{remote($1, @@>$2)} => @code{remote(year_2013,
+@@>$1)}. The format @code{B3} is not supported because it can not be
+distinguished from a plain table name or ID.
+
@node Formula syntax for Calc
@subsection Formula syntax for Calc
@cindex formula syntax, Calc
diff --git a/lisp/org-table.el b/lisp/org-table.el
index 36478f8..8c823d2 100644
--- a/lisp/org-table.el
+++ b/lisp/org-table.el
@@ -2657,6 +2657,7 @@ not overwrite the stored one."
;; Check for old vertical references
(setq form (org-table-rewrite-old-row-references form))
;; Insert remote references
+ (setq form (org-table-remote-reference-indirection form))
(while (string-match "\\<remote([ \t]*\\([-_a-zA-Z0-9]+\\)[ \t]*,[ \t]*\\([^\n)]+\\))" form)
(setq form
(replace-match
@@ -5010,6 +5011,30 @@ list of the fields in the rectangle."
(org-table-get-range (match-string 0 form) tbeg 1))
form)))))))))
+(defun org-table-remote-reference-indirection (form)
+ "Return formula with table remote references substituted by indirection.
+For example \"remote($1, @>$2)\" => \"remote(year_2013, @>$1)\".
+This indirection works only with the format @ROW$COLUMN. The
+format \"B3\" is not supported because it can not be
+distinguished from a plain table name or ID."
+ (while (string-match (concat
+ ;; Same as in `org-table-eval-formula'.
+ "\\<remote([ \t]*\\("
+ ;; Allow "$1", "@<", "$-1", "@<<$1" etc.
+ "[@$][^,]+"
+ ;; Same as in `org-table-eval-formula'.
+ "\\)[ \t]*,[ \t]*\\([^\n)]+\\))")
+ form)
+ ;; Substitute the remote reference with the value found in the
+ ;; field.
+ (setq form
+ (replace-match
+ (save-match-data
+ (org-table-get-range (org-table-formula-handle-first/last-rc
+ (match-string 1 form))))
+ t t form 1)))
+ form)
+
(defmacro org-define-lookup-function (mode)
(let ((mode-str (symbol-name mode))
(first-p (equal mode 'first))
diff --git a/testing/lisp/test-org-table.el b/testing/lisp/test-org-table.el
index a4f8caa..dd13fbf 100644
--- a/testing/lisp/test-org-table.el
+++ b/testing/lisp/test-org-table.el
@@ -837,14 +837,9 @@ See also `test-org-table/copy-field'."
")
1
;; Calc formula
- (concat "#+TBLFM: "
- "@2$2 = remote(2012, @>$1) :: "
- "@3$2 = remote(2013, @>$1) :: "
- "@>$2 = vsum(@I..@II)")
+ "#+TBLFM: @<<$2..@>>$2 = remote($<, @>$1) :: @>$2 = vsum(@I..@II)"
;; Lisp formula
- (concat "#+TBLFM: "
- "@2$2 = '(identity remote(2012, @>$1)); N :: "
- "@3$2 = '(identity remote(2013, @>$1)); N :: "
+ (concat "#+TBLFM: @<<$2..@>>$2 = '(identity remote($<, @>$1)); N :: "
"@>$2 = '(+ @I..@II); N"))
;; Read several remote references from same row
@@ -863,14 +858,9 @@ See also `test-org-table/copy-field'."
")
1
;; Calc formula
- (concat "#+TBLFM: "
- "@2$2 = remote(2012, @>$1) :: "
- "@2$3 = remote(2013, @>$1) :: "
- "@2$> = vsum($<<..$>>)")
+ "#+TBLFM: @2$<<..@2$>> = remote(@<, @>$1) :: @2$> = vsum($<<..$>>)"
;; Lisp formula
- (concat "#+TBLFM: "
- "@2$2 = '(identity remote(2012, @>$1)); N :: "
- "@2$3 = '(identity remote(2013, @>$1)); N :: "
+ (concat "#+TBLFM: @2$<<..@2$>> = '(identity remote(@<, @>$1)); N :: "
"@2$> = '(+ $<<..$>>); N"))))
(ert-deftest test-org-table/org-at-TBLFM-p ()
--
1.7.12.4 (Apple Git-37)
prev parent reply other threads:[~2014-01-04 15:29 UTC|newest]
Thread overview: 9+ messages / expand[flat|nested] mbox.gz Atom feed top
2012-11-16 11:33 sum up variables from different org-mode tables Martin Gross
2012-11-16 15:16 ` Michael Brand
2012-11-19 11:54 ` Martin Gross
2012-11-19 13:50 ` Michael Brand
2012-11-21 17:01 ` Martin Gross
2012-11-21 18:46 ` Suvayu Ali
2012-11-19 21:21 ` Eric Schulte
2012-11-22 10:03 ` Martin Gross
2014-01-04 15:29 ` Michael Brand [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=CALn3zoi0RAGZogUrfJuSpiTm_0r52sZxX8JzDjJzmr6oGDijJQ@mail.gmail.com \
--to=michael.ch.brand@gmail.com \
--cc=emacs-orgmode@gnu.org \
/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
Be sure your reply has a Subject: header at the top and a blank line
before the message body.
Code repositories for project(s) associated with this public 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).