From mboxrd@z Thu Jan 1 00:00:00 1970 From: Nick Dokos Subject: Re: Calculate differences of remote table numbers Date: Thu, 04 Oct 2018 12:37:52 -0400 Message-ID: <874le11xcv.fsf@alphaville.usersys.redhat.com> References: <2018-10-01T16-59-13@devnull.Karl-Voit.at> <87d0st1v5v.fsf@alphaville.usersys.redhat.com> <2018-10-04T09-23-01@devnull.Karl-Voit.at> Mime-Version: 1.0 Content-Type: text/plain Return-path: Received: from eggs.gnu.org ([2001:4830:134:3::10]:53842) by lists.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1g86da-0007WZ-BW for emacs-orgmode@gnu.org; Thu, 04 Oct 2018 12:38:07 -0400 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1g86dX-0006nk-K0 for emacs-orgmode@gnu.org; Thu, 04 Oct 2018 12:38:06 -0400 Received: from [195.159.176.226] (port=58126 helo=blaine.gmane.org) by eggs.gnu.org with esmtps (TLS1.0:RSA_AES_128_CBC_SHA1:16) (Exim 4.71) (envelope-from ) id 1g86dX-0006m3-BA for emacs-orgmode@gnu.org; Thu, 04 Oct 2018 12:38:03 -0400 Received: from list by blaine.gmane.org with local (Exim 4.84_2) (envelope-from ) id 1g86bN-0008CI-7b for emacs-orgmode@gnu.org; Thu, 04 Oct 2018 18:35:49 +0200 List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org Sender: "Emacs-orgmode" To: emacs-orgmode@gnu.org Karl Voit 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