From mboxrd@z Thu Jan  1 00:00:00 1970
From: tsd@tsdye.com (Thomas S. Dye)
Subject: Re: table formula help...
Date: Fri, 05 Dec 2014 21:37:06 -1000
Message-ID: <m2egsdkz4t.fsf@tsdye.com>
References: <87ppbxfi5f.fsf@ericabrahamsen.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Return-path: <emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org>
Received: from eggs.gnu.org ([2001:4830:134:3::10]:33841)
	by lists.gnu.org with esmtp (Exim 4.71)
	(envelope-from <tsd@tsdye.com>) id 1Xx9vn-00014e-GM
	for emacs-orgmode@gnu.org; Sat, 06 Dec 2014 02:37:37 -0500
Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71)
	(envelope-from <tsd@tsdye.com>) id 1Xx9vh-0004hP-2N
	for emacs-orgmode@gnu.org; Sat, 06 Dec 2014 02:37:31 -0500
Received: from gproxy6-pub.mail.unifiedlayer.com ([67.222.39.168]:57941)
	by eggs.gnu.org with smtp (Exim 4.71) (envelope-from <tsd@tsdye.com>)
	id 1Xx9vg-0004ek-Rf
	for emacs-orgmode@gnu.org; Sat, 06 Dec 2014 02:37:24 -0500
In-Reply-To: <87ppbxfi5f.fsf@ericabrahamsen.net> (Eric Abrahamsen's message of
	"Sat, 06 Dec 2014 13:42:52 +0800")
List-Id: "General discussions about Org-mode." <emacs-orgmode.gnu.org>
List-Unsubscribe: <https://lists.gnu.org/mailman/options/emacs-orgmode>,
	<mailto:emacs-orgmode-request@gnu.org?subject=unsubscribe>
List-Archive: <http://lists.gnu.org/archive/html/emacs-orgmode>
List-Post: <mailto:emacs-orgmode@gnu.org>
List-Help: <mailto:emacs-orgmode-request@gnu.org?subject=help>
List-Subscribe: <https://lists.gnu.org/mailman/listinfo/emacs-orgmode>,
	<mailto:emacs-orgmode-request@gnu.org?subject=subscribe>
Errors-To: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org
Sender: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org
To: Eric Abrahamsen <eric@ericabrahamsen.net>
Cc: emacs-orgmode@gnu.org

Aloha Eric,

One approach would normalize the =3Dcounts=3D table=3D and use a SQL query =
to
generate the payments table.

#+name: counts
| id | Piece | N    | Unit | Translator |
|  1 | foo   | 84   | line | bar        |
|  2 | baz   | 6633 | char | foobar     |

The query would sum over =3DN=3D conditional on =3DUnit=3D and group by
=3DTranslator=3D.  I can't write the query off the top of my head.

hth,
Tom

Eric Abrahamsen <eric@ericabrahamsen.net> writes:

> I never seem to use the spreadsheet unless it's some horribly
> complicated thing I don't know how to calculate... Can someone lend me a
> hand?
>
> I'm calculating payment rates for contributors (actually translators) to
> a magazine.
>
> I've got two tables: The first is essentially a table of contents,
> listing pieces with their character count (prose), or line count
> (poetry), plus who translated it.
>
> The second is a list of translators, with their total character/line
> count, and how much they're owed.
>
> I'm having a hell of a time getting the column formulas right:
> specifically referencing one table from another. The first table looks
> like this:
>
> #+NAME: counts
> | Piece          |  Chars | Lines | Translator       |
> +----------------+--------+-------+------------------+
> | =E5=87=A4=E5=87=B0           |        |    84 | Austin Woerner   |
> | =E7=8E=8B=E8=A1=80           |   6633 |       | Eric Abrahamsen  |
> | =E8=B5=B5=E6=B0=8F=E5=AD=A4=E5=84=BF       |  16984 |       | Canaan Mo=
rse     |
> | =E5=B1=B1=E9=AC=BC=E6=95=85=E5=AE=B6       |        |    24 | Lucas Kle=
in      |
> | =E5=AF=82=E9=9D=99=E4=BD=95=E5=85=B6=E6=B7=B1=E6=B2=89   |        |    =
10 | Lucas Klein      |
>
> And the second:
>
> #+NAME: payments
> #+CONSTANTS: prose=3D0.7 poetry=3D10
> | Translator       | Total Chars | Total Lines | Payment |
> |------------------+-------------+-------------+---------|
> | Austin Woerner   |             |             |         |
> | Eric Abrahamsen  |             |             |         |
> | Canaan Morse     |             |             |         |
> | Lucas Klein      |             |             |         |
>
> In this second table, the formula I want for the "Total Chars" column
> is:
>
> "Set each row of $2 in table payments to the sum of all numbers in $2 of
> remote(counts), if $2 is not empty, and if $4 of remote(counts) is equal
> to $1 of table payments."
>
> The formula for "Total Lines" would be exactly the same, but with the
> $2s all switched to $3. I can handle adding up the amounts!
>
> Is it possible to iterate over all the rows in another table like this?
> Is there a better way I could be arranging my tables?
>
> Thanks for any tips!
>
> Eric
>
>
>

--=20
Thomas S. Dye
http://www.tsdye.com