Calculate differences of remote table numbers

Calculate differences of remote table numbers

[Karl Voit]

Hi!

I'd like to calculate the differences between rows of numbers of a
different table.

Here is a minimal example showing the issue:

    #+NAME: my-table
    | Numbers |
    |---------|
    |       1 |
    |       5 |
    |       8 |
    |      12 |
    |      15 |

This is what I expected: "exp. Reference1" and "exp. Reference2" is here only
to demonstrate that the assumed references are wrong in the third table. "exp.
Difference" is the column I want to get in the first place. I don't want to see
Reference 1 or 2 at all in my solution (since I need a couple of those
difference-columns).

    | Line | exp. Reference1 | exp. Reference2 | exp. Difference |
    |------+-----------------+-----------------+-----------------|
    |    1 |               0 |               0 |               0 |
    |    2 |               5 |               1 |               4 |
    |    3 |               8 |               5 |               3 |
    |    4 |              12 |               8 |               4 |
    |    5 |              15 |              12 |               3 |

This is what I get instead:

    | Line | actual Reference1 | actual Reference2 | Difference |
    |------+-------------------+-------------------+------------|
    |    1 |                 0 |                 0 |          0 |
    |    2 |                 6 |                 1 |          5 |
    |    3 |                12 |                 1 |         11 |
    |    4 |                20 |                 1 |         19 |
    |    5 |                30 |                 1 |         29 |
    #+TBLFM: $2=if(2 == @#, 0, remote(my-table,@#$1))::$3=if(2 == @#, 0, remote(my-table,@#-1$1))::$4=if(2 == @#, 0, (remote(my-table,@#$1)-remote(my-table,@#-1$1)))

As you can see, the "actual Reference1" are not the values from the first
column above (except the first one). "actual Reference2" is not the shifted
column 1 from above (except the first one). I have no clue, what these numbers
are.

Maybe you can help me (1) explaining what the numbers in actual Reference1 and
actual Reference2 are showing and (2) how to get the result ("expected
Difference"; without explicitly generating Reference 1 or 2).

Thanks!


-- 
get mail|git|SVN|photos|postings|SMS|phonecalls|RSS|CSV|XML into Org-mode:
       > get Memacs from https://github.com/novoid/Memacs <
Personal Information Management > http://Karl-Voit.at/tags/pim/
Emacs-related > http://Karl-Voit.at/tags/emacs/

Re: Calculate differences of remote table numbers

[Nick Dokos]

Karl Voit <devnull@Karl-Voit.at> writes:

> Hi!
>
> I'd like to calculate the differences between rows of numbers of a
> different table.
>
> Here is a minimal example showing the issue:
>
>     #+NAME: my-table
>     | Numbers |
>
>     |---------|
>     |       1 |
>     |       5 |
>     |       8 |
>     |      12 |
>     |      15 |
>
> This is what I expected: "exp. Reference1" and "exp. Reference2" is here only
> to demonstrate that the assumed references are wrong in the third table. "exp.
> Difference" is the column I want to get in the first place. I don't want to see
> Reference 1 or 2 at all in my solution (since I need a couple of those
> difference-columns).
>
>     | Line | exp. Reference1 | exp. Reference2 | exp. Difference |
>     |------+-----------------+-----------------+-----------------|
>     |    1 |               0 |               0 |               0 |
>     |    2 |               5 |               1 |               4 |
>     |    3 |               8 |               5 |               3 |
>     |    4 |              12 |               8 |               4 |
>     |    5 |              15 |              12 |               3 |
>
> This is what I get instead:
>
>     | Line | actual Reference1 | actual Reference2 | Difference |
>     |------+-------------------+-------------------+------------|
>     |    1 |                 0 |                 0 |          0 |
>     |    2 |                 6 |                 1 |          5 |
>     |    3 |                12 |                 1 |         11 |
>     |    4 |                20 |                 1 |         19 |
>     |    5 |                30 |                 1 |         29 |
>
>     #+TBLFM: $2=if(2 == @#, 0, remote(my-table,@#$1))::$3=if(2 == @#, 0, remote(my-table,@#-1$1))::$4=if(2 == @#, 0, (remote(my-table,@#$1)-remote(my-table,@#-1$1)))

@# is the row number, so to refer to that row, you need @@#. Something like this,
although I didn't test to see if everything gets parsed correctly:

#+TBLFM: $2=if(2 == @#, 0, remote(my-table,@@#$1))::$3=if(2 == @#, 0, remote(my-table,@@#-1$1))::$4=if(2 == @#, 0, (remote(my-table,@@#$1)-remote(my-table,@@#-1$1)))

Turning on formula debugging with `C-c {' helps.

-- 
Nick

"There are only two hard problems in computer science: cache
invalidation, naming things, and off-by-one errors." -Martin Fowler

Re: Calculate differences of remote table numbers

[Karl Voit]

Hi Nick,

* Nick Dokos <ndokos@gmail.com> wrote:
> Karl Voit <devnull@Karl-Voit.at> writes:
>
>> I'd like to calculate the differences between rows of numbers of a
>> different table.
>>
>> Here is a minimal example showing the issue:
>>
>>     #+NAME: my-table
>>     | Numbers |
>>
>>     |---------|
>>     |       1 |
>>     |       5 |
>>     |       8 |
>>     |      12 |
>>     |      15 |
>>
>> This is what I expected: "exp. Reference1" and "exp. Reference2" is here only
>> to demonstrate that the assumed references are wrong in the third table. "exp.
>> Difference" is the column I want to get in the first place. I don't want to see
>> Reference 1 or 2 at all in my solution (since I need a couple of those
>> difference-columns).
>>
>>     | Line | exp. Reference1 | exp. Reference2 | exp. Difference |
>>     |------+-----------------+-----------------+-----------------|
>>     |    1 |               0 |               0 |               0 |
>>     |    2 |               5 |               1 |               4 |
>>     |    3 |               8 |               5 |               3 |
>>     |    4 |              12 |               8 |               4 |
>>     |    5 |              15 |              12 |               3 |
>>
>> This is what I get instead:
>>
>>     | Line | actual Reference1 | actual Reference2 | Difference |
>>     |------+-------------------+-------------------+------------|
>>     |    1 |                 0 |                 0 |          0 |
>>     |    2 |                 6 |                 1 |          5 |
>>     |    3 |                12 |                 1 |         11 |
>>     |    4 |                20 |                 1 |         19 |
>>     |    5 |                30 |                 1 |         29 |
>
> @# is the row number, so to refer to that row, you need @@#. Something like this,
> although I didn't test to see if everything gets parsed correctly:
>
> #+TBLFM: $2=if(2 == @#, 0, remote(my-table,@@#$1))::$3=if(2 == @#, 0, remote(my-table,@@#-1$1))::$4=if(2 == @#, 0, (remote(my-table,@@#$1)-remote(my-table,@@#-1$1)))

This is a huge step forward:

#+NAME: my-table
| Numbers |
|---------|
|       1 |
|       5 |
|       8 |
|      12 |
|      15 |

| Line | exp. Reference1 | exp. Reference2 | exp. Difference |
|------+-----------------+-----------------+-----------------|
|    1 |               0 |               0 |               0 |
|    2 |               5 |               1 |               4 |
|    3 |               8 |               5 |               3 |
|    4 |              12 |               8 |               4 |
|    5 |              15 |              12 |               3 |


| Line | actual Reference1 | actual Reference2 | Difference |
|------+-------------------+-------------------+------------|
|    1 |                 0 |                 0 |          0 |
|    2 |                 5 |                 5 |          0 |
|    3 |                 8 |                 8 |          0 |
|    4 |                12 |                12 |          0 |
|    5 |                15 |                15 |          0 |
#+TBLFM: $2=if(2 == @#, 0, remote(my-table,@@#$1))::$3=if(2 == @#, 0, remote(my-table,@@#-1$1))::$4=if(2 == @#, 0, (remote(my-table,@@#$1)-remote(my-table,@@#-1$1)))

Somehow, the "-1" in "@@#-1$1" does not have any effect: "@@#$1" and
"@@#-1$" gives the same results.

Do you have any idea why?

> Turning on formula debugging with `C-c {' helps.

Woha! I didn't know that! And I also didn't know =C-c }= for
displaying column/row numbers :-O *This* is going to be handy for me
;-)

-- 
get mail|git|SVN|photos|postings|SMS|phonecalls|RSS|CSV|XML into Org-mode:
       > get Memacs from https://github.com/novoid/Memacs <
Personal Information Management > http://Karl-Voit.at/tags/pim/
Emacs-related > http://Karl-Voit.at/tags/emacs/

Re: Calculate differences of remote table numbers

[Nick Dokos]

Karl Voit <devnull@Karl-Voit.at> writes:

>>
>> @# is the row number, so to refer to that row, you need @@#. Something like this,
>> although I didn't test to see if everything gets parsed correctly:
>>
>> #+TBLFM: $2=if(2 == @#, 0, remote(my-table,@@#$1))::$3=if(2 == @#,
>> 0, remote(my-table,@@#-1$1))::$4=if(2 == @#, 0,
>> (remote(my-table,@@#$1)-remote(my-table,@@#-1$1)))
>
> This is a huge step forward:
>
> #+NAME: my-table
> | Numbers |
>
> |---------|
> |       1 |
> |       5 |
> |       8 |
> |      12 |
> |      15 |
>
> | Line | exp. Reference1 | exp. Reference2 | exp. Difference |
> |------+-----------------+-----------------+-----------------|
> |    1 |               0 |               0 |               0 |
> |    2 |               5 |               1 |               4 |
> |    3 |               8 |               5 |               3 |
> |    4 |              12 |               8 |               4 |
> |    5 |              15 |              12 |               3 |
>
>
> | Line | actual Reference1 | actual Reference2 | Difference |
> |------+-------------------+-------------------+------------|
> |    1 |                 0 |                 0 |          0 |
> |    2 |                 5 |                 5 |          0 |
> |    3 |                 8 |                 8 |          0 |
> |    4 |                12 |                12 |          0 |
> |    5 |                15 |                15 |          0 |
>
> #+TBLFM: $2=if(2 == @#, 0, remote(my-table,@@#$1))::$3=if(2 == @#, 0, remote(my-table,@@#-1$1))::$4=if(2 == @#, 0, (remote(my-table,@@#$1)-remote(my-table,@@#-1$1)))
>
> Somehow, the "-1" in "@@#-1$1" does not have any effect: "@@#$1" and
> "@@#-1$" gives the same results.
>
> Do you have any idea why?

Yes, that's what I was afraid of when I said "...if everything is parsed correctly".
AFAICT, you cannot use expressions (I tried parentheses and curly braces as well: the
formula debugger complains that it expected a number.

One workaround is to add a column to the original table where you *can* calculate
using row/column expressions and then use each column where appropriate in the
remote references. Something like this:

--8<---------------cut here---------------start------------->8---
#+NAME: my-table
| Numbers | shifted |
|---------+---------|
|       1 |       0 |
|       5 |       1 |
|       8 |       5 |
|      12 |       8 |
|      15 |      12 |
#+TBLFM: @2$2..@>$2 = if(2==@#, 0, @-1$1)

| Line | exp. Reference1 | exp. Reference2 | exp. Difference |
|------+-----------------+-----------------+-----------------|
|    1 |               0 |               0 |               0 |
|    2 |               5 |               1 |               4 |
|    3 |               8 |               5 |               3 |
|    4 |              12 |               8 |               4 |
|    5 |              15 |              12 |               3 |

| Line | actual Reference1 | actual Reference2 | Difference |
|------+-------------------+-------------------+------------|
|    1 |                 0 |                 0 |          0 |
|    2 |                 5 |                 1 |          4 |
|    3 |                 8 |                 5 |          3 |
|    4 |                12 |                 8 |          4 |
|    5 |                15 |                12 |          3 |
#+TBLFM: $2=if(2 == @#, 0, remote(my-table,@@#$1))::$3=if(2 == @#, 0, remote(my-table,@@#$2))::$4=$2-$3
--8<---------------cut here---------------end--------------->8---

But somebody (perhaps Michael Brand, if he is around) might come up
with better ideas.

>
>> Turning on formula debugging with `C-c {' helps.
>
> Woha! I didn't know that! And I also didn't know =C-c }= for
> displaying column/row numbers :-O *This* is going to be handy for me
> ;-)

Both Emacs and Org Mode are unending sources of new discovery and
delight!-)

-- 
Nick

"There are only two hard problems in computer science: cache
invalidation, naming things, and off-by-one errors." -Martin Fowler

Re: Calculate differences of remote table numbers

[Michael Brand]

Hi Karl

On Mon, Oct 1, 2018 at 5:02 PM Karl Voit <devnull@karl-voit.at> wrote:

> I'd like to calculate the differences between rows of numbers of a
> different table.

For this kind of shifting row or column indexes I use Calc vector
subscript. In your case:

#+NAME: my-table
| Numbers |
|---------|
|       1 |
|       5 |
|       8 |
|      12 |
|      15 |

| Line | Difference |
|------+------------|
|    1 |            |
|    2 |          4 |
|    3 |          3 |
|    4 |          4 |
|    5 |          3 |
#+TBLFM: $2 = if($1 == 1, string(""), subscr(remote(my-table,
@I$1..@II$1), @# - 1) - subscr(remote(my-table, @I$1..@II$1), @# - 2))

or, avoiding @# completely in the formula for $2:

#+TBLFM: $2 = if($1 == 1, string(""), subscr(remote(my-table,
@I$1..@II$1), $1) - subscr(remote(my-table, @I$1..@II$1), $1 - 1))

See also a similar example of subscr in the subsection "Dynamic
variation of ranges" here:
https://orgmode.org/worg/org-hacks.html#field-coordinates-in-formulas

Michael

Re: Calculate differences of remote table numbers

[Karl Voit]

* Michael Brand <michael.ch.brand@gmail.com> wrote:
> Hi Karl

Hi Michael,

> On Mon, Oct 1, 2018 at 5:02 PM Karl Voit <devnull@karl-voit.at> wrote:
>
>> I'd like to calculate the differences between rows of numbers of a
>> different table.
>
> For this kind of shifting row or column indexes I use Calc vector
> subscript. In your case:
>
> #+NAME: my-table
>| Numbers |
>|---------|
>|       1 |
>|       5 |
>|       8 |
>|      12 |
>|      15 |
>
>| Line | Difference |
>|------+------------|
>|    1 |            |
>|    2 |          4 |
>|    3 |          3 |
>|    4 |          4 |
>|    5 |          3 |
> #+TBLFM: $2 = if($1 == 1, string(""), subscr(remote(my-table,
> @I$1..@II$1), @# - 1) - subscr(remote(my-table, @I$1..@II$1), @# - 2))
>
> or, avoiding @# completely in the formula for $2:
>
> #+TBLFM: $2 = if($1 == 1, string(""), subscr(remote(my-table,
> @I$1..@II$1), $1) - subscr(remote(my-table, @I$1..@II$1), $1 - 1))
>
> See also a similar example of subscr in the subsection "Dynamic
> variation of ranges" here:
> https://orgmode.org/worg/org-hacks.html#field-coordinates-in-formulas

Thanks for your post - it is working as expected! \o/

Also thanks for the URL - I added it to my knowledge base.

Unfortunately, I have to admit that spreadsheets in Org are not
something I can recommend to somebody who needs "something that just
works" (like Excel-switcher). :-(

However, when a table is finally working as expected, I really do
appreciate having this table (and its derived data) within my notes
and not locked away in a separate file.

-- 
get mail|git|SVN|photos|postings|SMS|phonecalls|RSS|CSV|XML into Org-mode:
       > get Memacs from https://github.com/novoid/Memacs <
Personal Information Management > http://Karl-Voit.at/tags/pim/
Emacs-related > http://Karl-Voit.at/tags/emacs/

Re: Calculate differences of remote table numbers

[Eric S Fraga]

On Monday,  8 Oct 2018 at 17:19, Karl Voit wrote:
> Unfortunately, I have to admit that spreadsheets in Org are not
> something I can recommend to somebody who needs "something that just
> works" (like Excel-switcher). :-(

Maybe not but the combination of "It's all text", Emacs calc, and LaTeX export is unbeatable for me.  I've not used any other spreadsheet in years!

-- 

Eric S Fraga via Emacs 27.0.50, Org release_9.1.13-894-gf79545

Complexity of using spreadsheet functionality (was: Calculate differences of remote table numbers)

[Karl Voit]

* Eric S Fraga <esflists@gmail.com> wrote:
> On Monday,  8 Oct 2018 at 17:19, Karl Voit wrote:
>> Unfortunately, I have to admit that spreadsheets in Org are not
>> something I can recommend to somebody who needs "something that just
>> works" (like Excel-switcher). :-(
>
> Maybe not but the combination of "It's all text", Emacs calc, and
> LaTeX export is unbeatable for me.  I've not used any other
> spreadsheet in years!

I do think that we all agree on that.

But it's more "I accept the nasty issues I have while creating
complex spreadsheet stuff because I got tons of advantages elsewhere
in this tool" instead of "hey, you're using Excel. Wanna switch to
Org/calc because it offers equivalent spreadsheet functionality on
top of many other advantages".

You have to use Org for a while in order to be able to grasp the
large universe of possibilities. It's really not that easy to
explain or show. It's something you have to experience IMHO. And
after you had this epiphany, you accept even hard to do things like
complex calc references.

-- 
get mail|git|SVN|photos|postings|SMS|phonecalls|RSS|CSV|XML into Org-mode:
       > get Memacs from https://github.com/novoid/Memacs <
Personal Information Management > http://Karl-Voit.at/tags/pim/
Emacs-related > http://Karl-Voit.at/tags/emacs/

Re: Complexity of using spreadsheet functionality

[Eric S Fraga]

On Monday,  8 Oct 2018 at 18:16, Karl Voit wrote:
> You have to use Org for a while in order to be able to grasp the
> large universe of possibilities. It's really not that easy to
> explain or show. It's something you have to experience IMHO. And
> after you had this epiphany, you accept even hard to do things like
> complex calc references.

I think that's definitely the case.  There are things in org that should
be easier.  However, the one really good feature of org is that it is
easy to start with the simple outline and task management capabilities
and then slowly discover the other very powerful features.

-- 
Eric S Fraga via Emacs 27.0.50, Org release_9.1.13-783-g97fac4

Re: Complexity of using spreadsheet functionality (was: Calculate differences of remote table numbers)

[Samuel Wales]

me too on spreadsheets are challenging in org [but powerful].  i
wonder what would make them more intuitive.

i use them infrequently, so i have to re-learn them when i use them.

it would be really neat if we could do something like c-c ' that pulls
up a more immediately intuitive interface.  maybe an ses mode type
spreadsheet?  for some tasks, some might even prefer a regular
spreadsheet whose serialized dot files get stored in an org block.
but that would not show the table in org, which you want also.