Table column formula with remote reference

Table column formula with remote reference

[Wu Ming]

Hello,

#+NAME: A
Table with header row here

#+NAME: B
Table with header row here
#+TBLFML: $1 = remote(A,$1)

Does nothing. 

$1 = remote(A,@0) referring current row w implicit column does not work. Debug shows on row 2 of current table it returns the value of row 1, the header, on table A. 

Same result as above with @0$1 instead. 

$1 = remote(A,@2$1) does what expected. For every row copies the same value of @2$1 from table A to table B. 

Need to refer multiple columns on different tables in a column formula for a final table. 

Please advise. Thanks. 

Re: Table column formula with remote reference

[Wu Ming]

[-- Attachment #1: Type: text/plain, Size: 984 bytes --]

Problem solved with

$1 = remote(A,@@#$1)

as described on the manual page https://orgmode.org/manual/References.html#Field-coordinates-in-formulas-1 <https://orgmode.org/manual/References.html#Field-coordinates-in-formulas-1>

Where is the coordinate @@# documented?


> On 11 Mar 2024, at 18:52, Wu Ming <wu.ming2@icloud.com> wrote:
> 
> Hello,
> 
> #+NAME: A
> Table with header row here
> 
> #+NAME: B
> Table with header row here
> #+TBLFML: $1 = remote(A,$1)
> 
> Does nothing. 
> 
> $1 = remote(A,@0) referring current row w implicit column does not work. Debug shows on row 2 of current table it returns the value of row 1, the header, on table A. 
> 
> Same result as above with @0$1 instead. 
> 
> $1 = remote(A,@2$1) does what expected. For every row copies the same value of @2$1 from table A to table B. 
> 
> Need to refer multiple columns on different tables in a column formula for a final table. 
> 
> Please advise. Thanks. 
> 
> 


[-- Attachment #2: Type: text/html, Size: 1810 bytes --]

Re: Table column formula with remote reference

[Ihor Radchenko]

Wu Ming <wu.ming2@icloud.com> writes:

> Problem solved with
>
> $1 = remote(A,@@#$1)
>
> as described on the manual page https://orgmode.org/manual/References.html#Field-coordinates-in-formulas-1 <https://orgmode.org/manual/References.html#Field-coordinates-in-formulas-1>
>
> Where is the coordinate @@# documented?

I am confused. Isn't it documented in the linked section of the manual?


-- 
Ihor Radchenko // yantar92,
Org mode contributor,
Learn more about Org mode at <https://orgmode.org/>.
Support Org development at <https://liberapay.com/org-mode>,
or support my work at <https://liberapay.com/yantar92>

Re: Table column formula with remote reference

[Wu Ming]

> On 12 Mar 2024, at 22:46, Ihor Radchenko <yantar92@posteo.net> wrote:
> 
> Wu Ming <wu.ming2@icloud.com> writes:
> 
>> Problem solved with
>> 
>> $1 = remote(A,@@#$1)
>> 
>> as described on the manual page https://orgmode.org/manual/References.html#Field-coordinates-in-formulas-1 <https://orgmode.org/manual/References.html#Field-coordinates-in-formulas-1>
>> 
>> Where is the coordinate @@# documented?
> 
> I am confused. Isn't it documented in the linked section of the manual?
> 
> 
> -- 
> Ihor Radchenko // yantar92,
> Org mode contributor,
> Learn more about Org mode at <https://orgmode.org/>.
> Support Org development at <https://liberapay.com/org-mode>,
> or support my work at <https://liberapay.com/yantar92>

My fault. Missed the line "One of the very first actions during evaluation of Calc formulas and Lisp formulas is to substitute ‘@#’ and ‘$#’ in the formula with the row or column number of the field where the current result will go to.“  So '@@#' becomes '@<current row>'. 

Overlooked it also because I did read the other line "‘@0’ and ‘$0’ refer to the current row and column, respectively, i.e., to the row/column for the field being computed.” and did try '@0$1'. Why is this different from the above?

Re: Table column formula with remote reference

[Ihor Radchenko]

Wu Ming <wu.ming2@icloud.com> writes:

> My fault. Missed the line "One of the very first actions during evaluation of Calc formulas and Lisp formulas is to substitute ‘@#’ and ‘$#’ in the formula with the row or column number of the field where the current result will go to.“  So '@@#' becomes '@<current row>'. 
>
> Overlooked it also because I did read the other line "‘@0’ and ‘$0’ refer to the current row and column, respectively, i.e., to the row/column for the field being computed.” and did try '@0$1'. Why is this different from the above?

See "Remote references" subsection. It explains that in
remote(NAME,REF), REF is inside the remote table. Relative and current
column/row is ambiguous there.

In contrast, @# and $# are special - they are replaced before
remote(...) is processed.

I agree that the manual is somewhat confusing. Possibly, we may even
change `org-table-get-remote-range' to use relative references according
to the original table. Improvements welcome!

-- 
Ihor Radchenko // yantar92,
Org mode contributor,
Learn more about Org mode at <https://orgmode.org/>.
Support Org development at <https://liberapay.com/org-mode>,
or support my work at <https://liberapay.com/yantar92>

Re: Table column formula with remote reference

[Wu Ming]

> On 13 Mar 2024, at 20:16, Ihor Radchenko <yantar92@posteo.net> wrote:
> 
> Wu Ming <wu.ming2@icloud.com> writes:
> 
>> My fault. Missed the line "One of the very first actions during evaluation of Calc formulas and Lisp formulas is to substitute ‘@#’ and ‘$#’ in the formula with the row or column number of the field where the current result will go to.“  So '@@#' becomes '@<current row>'. 
>> 
>> Overlooked it also because I did read the other line "‘@0’ and ‘$0’ refer to the current row and column, respectively, i.e., to the row/column for the field being computed.” and did try '@0$1'. Why is this different from the above?
> 
> See "Remote references" subsection. It explains that in
> remote(NAME,REF), REF is inside the remote table. Relative and current
> column/row is ambiguous there.
> 
> In contrast, @# and $# are special - they are replaced before
> remote(...) is processed.
> 
> I agree that the manual is somewhat confusing. Possibly, we may even
> change `org-table-get-remote-range' to use relative references according
> to the original table. Improvements welcome!
> 

I have some trouble at understanding your answer. Do you mean @# refers a row on the table where the formula belongs and @0 refers a row on the remote table? Was tempted to describe the former as “current” but remote table is also current when accessed. A better noun may be needed.


Unrelated, but appeared on the same trial, noticed a cell was mis-calculated. Could not pin-point the reason before error disappeared after running a side formula performing the same operation. Formula was simply copying column values from a remote table. Remote table values were never changed. Recalculate all and C-c C-c were performed multiple times before the curious fix.

This made me worry about reliability of simple biz calculations I am trying on Org spreadsheet for the first time. Please advise.


Finally I moved columns but now column numbers in formulas don’t relate to column order on display. How to understand which column formula affect which column?


Sorry to coalesce multiple questions in to one. They just came to me while typing.

Re: Table column formula with remote reference

[Fraga, Eric]

On Thursday, 14 Mar 2024 at 09:16, Wu Ming wrote:
> Unrelated, but appeared on the same trial, noticed a cell was
> mis-calculated. [...] This made me worry about reliability of simple
> biz calculations I am trying on Org spreadsheet for the first
> time. Please advise.

I've not seen any problems with spreadsheet/table calculations in org and use it extensively.  I don't use remote access generally however.

In any case, one very nice feature of org tables is you can see exactly how and what it calculates when you ask it to.  Turn on debugging by "C-c {" (org-table-toggle-formula-debugger) and you can see all the information you should need to identify what, if anything, is going wrong.

Turn off debugging with the same key sequence.

-- 
: Eric S Fraga, with org release_9.6.19-1215-g67d937 in Emacs 30.0.50

Re: Table column formula with remote reference

[Wu Ming]

> On 14 Mar 2024, at 9:40 PM, Fraga, Eric <e.fraga@ucl.ac.uk> wrote:
> 
> On Thursday, 14 Mar 2024 at 09:16, Wu Ming wrote:
>> Unrelated, but appeared on the same trial, noticed a cell was
>> mis-calculated. [...] This made me worry about reliability of simple
>> biz calculations I am trying on Org spreadsheet for the first
>> time. Please advise.
> 
> I've not seen any problems with spreadsheet/table calculations in org and use it extensively.  I don't use remote access generally however.
> 
> In any case, one very nice feature of org tables is you can see exactly how and what it calculates when you ask it to.  Turn on debugging by "C-c {" (org-table-toggle-formula-debugger) and you can see all the information you should need to identify what, if anything, is going wrong.
> 
> Turn off debugging with the same key sequence.

Thanks for the reference to formula debugger. In the heat of debugging an error as obvious, and worrying, as the one I saw forgot about it. Though I am still new to Emacs and Org so that’s not so surprising. 

I have one table retrieving data from two more. 18 columns x 7 rows total. I could have everything into one larger table but splitting makes them more readable I think. And possibly simplifies sharing end results. Haven’t tried Org export options yet. What is your organization system with tables?

Re: Table column formula with remote reference

[Fraga, Eric]

On Sunday, 17 Mar 2024 at 10:29, Wu Ming wrote:
> Haven’t tried Org export options yet. What is your organization system
> with tables?

I don't have a system!  Often, tables in my documents are the output of
some other code and the tables are the results that need further
processing (or visualising).  Most of my tables are standalone.  As I
think I mentioned earlier, I have seldom (but not never) made use of
remote references.

I do however often have multiple tables as inputs to the post-processing
or visualisation codes.

-- 
: Eric S Fraga, with org release_9.6.19-1215-g67d937 in Emacs 30.0.50

Re: Table column formula with remote reference

[Ihor Radchenko]

Wu Ming <wu.ming2@icloud.com> writes:

>> See "Remote references" subsection. It explains that in
>> remote(NAME,REF), REF is inside the remote table. Relative and current
>> column/row is ambiguous there.
>> 
>> In contrast, @# and $# are special - they are replaced before
>> remote(...) is processed.
> ...
> I have some trouble at understanding your answer. Do you mean @# refers a row on the table where the formula belongs and @0 refers a row on the remote table? Was tempted to describe the former as “current” but remote table is also current when accessed. A better noun may be needed.

Let me elaborate.

When Org mode sees something like

#+TBLFML: $1 = $2 + remote(A,@@#$1) 

1. it goes to every cell in column 1 and remembers current column and
   row numbers (original cell)

2. In the right side of the formula $2 + remote(A,@@#$1), Org replaces
   all the instances of @# and $# with current column and row.
   So, when we are calculating the value for @1$1, we get
   $2 + remote(A,@1$1)

3. Org moves to table A and replaces remote(A,@1$1) with cell contents
   of @1$1 inside table A. At this point, it is not allowed to have
   relative references like $1 or $-1, because "current" column and row
   are set inside remote table A - the original cell coordinates are not
   available.

4. Org goes back to the original table, takes the updated formula
   $2 + <remote value A@1$1>, and replaces relative reference $2
   according to the current column - with the value stored in @1$2
   column

5. Org passes the resulting expression <local value @1$2> + <remote
   value A@1$1> to GNU cal and assigns the result as the value of the
   current cell @1$1.

6. Repeat for @2..$1 cells.

As you can see, @# and $# substitution always uses local cell
coordinates. Any other relative reference is not allowed inside
remote(...).

> This made me worry about reliability of simple biz calculations I am trying on Org spreadsheet for the first time. Please advise.

Formula debugger is really helpful to understand the process.

> Finally I moved columns but now column numbers in formulas don’t relate to column order on display. How to understand which column formula affect which column?

Normally, if you use org-table-* commands, the formulas get updated when
you move the columns.

To make things more readable, you can also assign names to columns:

     | ! |         |     P1 |     P2 |     P3 |   Tot |      |
     |   | Maximum |     10 |     15 |     25 |    50 | 10.0 |

Then, you can write $P1 = ... instead of $3 = ...
See "3.5.10 Advanced features" section of the manual.

-- 
Ihor Radchenko // yantar92,
Org mode contributor,
Learn more about Org mode at <https://orgmode.org/>.
Support Org development at <https://liberapay.com/org-mode>,
or support my work at <https://liberapay.com/yantar92>

Re: Table column formula with remote reference

[Wu Ming]

> On 15 Mar 2024, at 2:58 AM, Ihor Radchenko <yantar92@posteo.net> wrote:
> 
> Wu Ming <wu.ming2@icloud.com> writes:
> 
>>> See "Remote references" subsection. It explains that in
>>> remote(NAME,REF), REF is inside the remote table. Relative and current
>>> column/row is ambiguous there.
>>> 
>>> In contrast, @# and $# are special - they are replaced before
>>> remote(...) is processed.
>> ...
>> I have some trouble at understanding your answer. Do you mean @# refers a row on the table where the formula belongs and @0 refers a row on the remote table? Was tempted to describe the former as “current” but remote table is also current when accessed. A better noun may be needed.
> 
> Let me elaborate.
> 
> When Org mode sees something like
> 
> #+TBLFML: $1 = $2 + remote(A,@@#$1) 
> 
> 1. it goes to every cell in column 1 and remembers current column and
>   row numbers (original cell)
> 
> 2. In the right side of the formula $2 + remote(A,@@#$1), Org replaces
>   all the instances of @# and $# with current column and row.
>   So, when we are calculating the value for @1$1, we get
>   $2 + remote(A,@1$1)
> 
> 3. Org moves to table A and replaces remote(A,@1$1) with cell contents
>   of @1$1 inside table A. At this point, it is not allowed to have
>   relative references like $1 or $-1, because "current" column and row
>   are set inside remote table A - the original cell coordinates are not
>   available.
> 
> 4. Org goes back to the original table, takes the updated formula
>   $2 + <remote value A@1$1>, and replaces relative reference $2
>   according to the current column - with the value stored in @1$2
>   column
> 
> 5. Org passes the resulting expression <local value @1$2> + <remote
>   value A@1$1> to GNU cal and assigns the result as the value of the
>   current cell @1$1.
> 
> 6. Repeat for @2..$1 cells.
> 
> As you can see, @# and $# substitution always uses local cell
> coordinates. Any other relative reference is not allowed inside
> remote(...).
> 

Very clear now. Thank you. But I was mostly confounded by references $0 and #0 versus the @@# (and $$#) you just described the processing of. Don’t want to abuse your time. I can figure it out when needed. But if you feel inclined to unravel this little detail of the manual as well I would clearly appreciate the effort. 

>> This made me worry about reliability of simple biz calculations I am trying on Org spreadsheet for the first time. Please advise.
> 
> Formula debugger is really helpful to understand the process.
> 
>> Finally I moved columns but now column numbers in formulas don’t relate to column order on display. How to understand which column formula affect which column?
> 
> Normally, if you use org-table-* commands, the formulas get updated when
> you move the columns.

One side effect of using remote formulas is re-organizing columns doesn’t update them automatically. I should find the balance of readability and formulas maintenance cost. But you may have suggested the solution below already with named columns.
> 
> To make things more readable, you can also assign names to columns:
> 
>     | ! |         |     P1 |     P2 |     P3 |   Tot |      |
>     |   | Maximum |     10 |     15 |     25 |    50 | 10.0 |
> 
> Then, you can write $P1 = ... instead of $3 = ...
> See "3.5.10 Advanced features" section of the manual.

Clever. And we are at the “Advanced“ features already. Are advanced-advanced in the realm of Calc? 

Asking because was also wondering how to optimize parameters (“solver”) and deal with locales (“,” vs “.” separators). For the latter I could possibly ‘tr’ them before sharing the output. But will possibly mess the alignment. Happened while trialling groff’s tbl.

Re: Table column formula with remote reference

[Ihor Radchenko]

Wu Ming <wu.ming2@icloud.com> writes:

> Very clear now. Thank you. But I was mostly confounded by references
> $0 and #0 versus the @@# (and $$#) you just described the processing
> of. Don’t want to abuse your time. I can figure it out when needed.
> But if you feel inclined to unravel this little detail of the manual
> as well I would clearly appreciate the effort.

The main difference is that @# always refer to the original table, while
$0 may refer to other tables as well.

(Generally, reference expansion process is not well documented,
unfortunately; it would be nice if somebody wrote a documentation
explaining the process - things can get tricky in some edge cases)

>> Normally, if you use org-table-* commands, the formulas get updated when
>> you move the columns.
>
> One side effect of using remote formulas is re-organizing columns doesn’t update them automatically. I should find the balance of readability and formulas maintenance cost. But you may have suggested the solution below already with named columns.

In theory, we might try to update such remote references at least in
current buffer. Contributions welcome.

>> To make things more readable, you can also assign names to columns:
>> 
>>     | ! |         |     P1 |     P2 |     P3 |   Tot |      |
>>     |   | Maximum |     10 |     15 |     25 |    50 | 10.0 |
>> 
>> Then, you can write $P1 = ... instead of $3 = ...
>> See "3.5.10 Advanced features" section of the manual.
>
> Clever. And we are at the “Advanced“ features already. Are advanced-advanced in the realm of Calc? 

> Asking because was also wondering how to optimize parameters (“solver”) and deal with locales (“,” vs “.” separators). For the latter I could possibly ‘tr’ them before sharing the output. But will possibly mess the alignment. Happened while trialling groff’s tbl.

AFAIK, GNU calc does not support comma as decimal point as _input_. For
output, I am not sure.

-- 
Ihor Radchenko // yantar92,
Org mode contributor,
Learn more about Org mode at <https://orgmode.org/>.
Support Org development at <https://liberapay.com/org-mode>,
or support my work at <https://liberapay.com/yantar92>