Hi Alan,
seems what you're looking for is some relational database functionality, which
is not possible with org spreadsheets :-(
Find a possible solution for your problem in the attached org file. You will
need org-babel to execute the source blocks.
hth,
Stephan
Also sprach Alan Boudreault:
> Hi Sébastien and thank you for your answer.
>
> The problem with that technique is that I'll have to name ALL the cell
> for ALL the colomn. Here's an example of my table:
>
> #+TBLNAME: FOOD
> | Name | Brand | Amounts (g) | Calories | Protein |
> Carbohydrate | Fat |
>
> |-----------------+---------------+-------------+----------+---------+--------------+------|
>
> | Poitrine Poulet | KirkLand | 100 | 100 |
> 24 | 0 | 1 |
> | Capeli d'angelo | Primo | 85 | 300 | 10
> | 63 | 1 |
> | Couscous | Clic | 62 | 220
> | 8 | 46 | 1 |
> | Fromage Cottage | Sealtest | 125 | 100 | 15
> | 8 | 1 |
> | Ficello | Black Diamond | 21 | 60
> | 6 | 0 | 4 |
> | Concombre cru | | 100 | 15 | 0.65
> | 3.63 | 0.11 |
> | Clementine cru | | 100 | 47 | 0.85
> | 12.02 | 0.15 |
>
> In the table 2, I would like to be able to just set a column to ie. 2
> (for "Poitrine Poulet"), then I'll be able to get all the info of the
> different columns I need to do my calculs. I see there is also a "_"
> option for row name, but I'm unsure on how to specify the column desired
> with that name and how to get it evaluated if I need to concat something...
>
> regards,
> Alan
>
> Sébastien Vauban wrote:
>> Hi Alan,
>>
>> Alan Boudreault wrote:
>>
>>> Here's what I'm trying to do (without success) in my spreadsheet:
>>>
>>> I have a first table :
>>>
>>> #+TBLNAME: table1
>>> | ... |... |.... |...|...
>>>
>>> and a second table:
>>>
>>> #+TBLNAME: table2
>>> | line_reference | ... |... |.... |...|...
>>>
>>> The line_reference is the line number to get some information in the
>>> table1.
>>> So in a cell of the table2, I tried a formula like:
>>> remote(table1,@$1$3) ($1
>>> = the line_reference) which of course, didn't work.
>>>
>>> then I tried to use elisp: remote(table1,'(concat "@" $1 "$3")') ....
>>> but no
>>> success anyway.
>>>
>>> after all, I decided to merge my 2 tables because it looks like I can
>>> use
>>> elisp inside a org function (remote). The last tried formula was:
>>> '(concat
>>> "@" $1 "$3")' ,($1 = the line_reference). The problem of this formula is
>>> only that I got the string "@2$3" inside the cell and it's not
>>> evaluated as
>>> a formula.
>>>
>>> Could anyone give me some hint to get done what I'm trying to do?
>>>
>>
>> What I do, which works:
>>
>> - give a name to the cell in table1
>> - reference it (in table2) by its name
>>
>>
>> #+TBLNAME: Eau
>> | | Date | Relevé m3 | Consommation | Prix TVAC |
>> |---+------------------+-----------+--------------+-----------|
>> | | [2009-11-15 Sun] | 2072.6263 | 0.0000 | 0.00 |
>> | | [2009-11-22 Sun] | 2074.1774 | 1.5511 | 1.55 |
>> | | [2009-11-29 Sun] | 2076.0834 | 1.9060 | 1.91 |
>> | | [2009-12-06 Sun] | 2077.6757 | 1.5923 | 1.59 |
>> | | [2009-12-13 Sun] | 2079.2140 | 1.5383 | 1.54 |
>> | | [2009-12-20 Sun] | 2081.4222 | 2.2082 | 2.21 |
>> | | [2009-12-27 Sun] | 2083.2146 | 1.7924 | 1.79 |
>> | | [2010-01-03 Sun] | 2085.4916 | 2.2770 | 2.28 |
>> | | [2010-01-10 Sun] | 2087.4283 | 1.9367 | 1.94 |
>> | ^ | | | | Eau |
>> #+TBLFM: $...@+0$-1-@-1$-1;%.4f::$5=$4*1;%.2f::@2$4=0;%.4f
>>
>>
>> | Énergie | Prix TVAC |
>> |-------------+-----------|
>> | Eau | 1.94 |
>> | Gaz | 156.81 |
>> | Électricité | 11.64 |
>> |-------------+-----------|
>> | Total | 170.39 |
>> #+TBLFM:
>> @2$2=remote(Eau,$Eau)::@3$2=remote(Gaz,$Gaz)::@4$2=remote(Elec,$Elec)::@5$2=vsum(@-...@-ii);%.2f
>>
>>
>> Best regards,
>> Seb
>>
>>
>
>
* select from where
To execute the source blocks load org-babel and hit "C-c C-C" with
point inside the block.
The following function implements a simple SELECT-FROM-WHERE clause
for remote org-tables:
#+srcname: my-select-from-where
#+begin_src emacs-lisp :tangle no :results silent
(require 'org-babel-ref)
(defun my-select-from-where (select-column table-name where-column where-entry)
"Get an entry of a remote table with #+TBLNAME: `table-name'
using a simple where clause:
SELECT select-column FROM table-name WHERE where-column = where-entry
`select-column' and `where-column' are zero-based indices (first
column has index 0). The test is done using `equal'.
Example:
For the following tabe
,#+TBLNAME: mytable
| 0815 | foo |
| 4711 | bar |
the function call
(my-select-from-where 1 \"mytable\" 0 4711)
returns \"bar\"."
(let ((table (org-babel-ref-resolve-reference table-name nil))
select-entry)
(dolist (row table)
(if (equal (nth where-column row) where-entry)
(if (null select-entry)
(setq select-entry (nth select-column row))
(error "where-entry is not unique"))))
select-entry))
#+end_src
This requires the column which is used for indexing to have unique
entries.
Let's take this table for testing:
#+TBLNAME: mytable1
| 0815 | foo |
| 4711 | bar |
First select the second column of the row where the first column
equals 0815, then select the first column of the row where the
second column equals "foo":
#+srcname: test-select
#+begin_src emacs-lisp :tangle no
(list
(my-select-from-where 1 "mytable1" 0 0815)
(my-select-from-where 0 "mytable1" 1 "foo"))
#+end_src
Unfortunately, a table name ("mytable1") is replaced by something
obscure, when we use it in the #+TBLFM line. Also, the function
my-select-from-where converts numeric table entries to a numeric
type, in contrast to the referencing in the #+TBLFM line. These
helper functions work around these problems:
#+srcname: my-select-from-where-helpers
#+begin_src emacs-lisp :tangle no :results silent
(defun my-select-1-from-table-where-0= (where-entry)
(my-select-from-where 1 "mytable1" 0
(if (numberp where-entry) where-entry
(string-to-number where-entry))))
(defun my-select-0-from-table-where-1= (where-entry)
(my-select-from-where 0 "mytable1" 1 where-entry))
#+end_src
Testing the helpers:
#+srcname: test-helpers
#+begin_src emacs-lisp :tangle no
(list
(my-select-1-from-table-where-0= 4711)
(my-select-0-from-table-where-1= "bar"))
#+end_src
Now we can reference remote table rows using a dynamic index (press
"C-c C-c" in the #+TBLFM line):
#+TBLNAME: mytable2
| 4711 | | |
| 0815 | | |
#+TBLFM: $2='(my-select-1-from-table-where-0= $1)::$3='(my-select-0-from-table-where-1= $2)
_______________________________________________
Emacs-orgmode mailing list
Please use `Reply All' to send replies to the list.
Emacs-orgmode@gnu.org
http://lists.gnu.org/mailman/listinfo/emacs-orgmode
