From mboxrd@z Thu Jan 1 00:00:00 1970 From: Shankar Rao Subject: Re: How to cross reference more than two table columns? Date: Sun, 11 Oct 2015 12:53:08 -0700 Message-ID: Mime-Version: 1.0 Content-Type: multipart/alternative; boundary=001a113ce1bcdadbe20521d98f34 Return-path: Received: from eggs.gnu.org ([2001:4830:134:3::10]:38490) by lists.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1ZlMgA-0005fb-Jf for emacs-orgmode@gnu.org; Sun, 11 Oct 2015 15:53:11 -0400 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1ZlMg9-0002VB-BO for emacs-orgmode@gnu.org; Sun, 11 Oct 2015 15:53:10 -0400 Received: from mail-oi0-x22d.google.com ([2607:f8b0:4003:c06::22d]:33838) by eggs.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1ZlMg9-0002Ui-5d for emacs-orgmode@gnu.org; Sun, 11 Oct 2015 15:53:09 -0400 Received: by oiak8 with SMTP id k8so52781655oia.1 for ; Sun, 11 Oct 2015 12:53:08 -0700 (PDT) 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-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org To: emacs-orgmode@gnu.org --001a113ce1bcdadbe20521d98f34 Content-Type: text/plain; charset=UTF-8 Sorry forgot to put in a subject On Sun, Oct 11, 2015 at 12:51 PM, Shankar Rao wrote: > I have multiple savings accounts, some of which I want to partition into > mutiple virtual "subaccounts" that don't merit their own savings account. I > want to use org-mode tables to keep track how much money is each > subaccount. Given the following table: > > #+TBLNAME: trans-150925 > | ! | Amount | From | To | Subacct | > |---+--------+------------+------------+-------------| > | | 50 | Short Term | Inbox | Bond | > | | 200 | Inbox | Short Term | Bond | > | | 120 | Gifts | Inbox | Baby Gifts | > | | 75 | Short Term | Inbox | Furnishings | > > In this example, I have accounts "Inbox", "Short Term", and "Gifts". Of > these accounts, "Short Term" has two subaccounts "Bond" and "Furnishings", > and "Gifts" has a subaccount "Baby Gifts". I want to create a function that > given a subaccount name, calculates the net amount to be > withdrawn/deposited to the subaccount. Note that "Inbox" doesn't have any > subaccounts, which I want to use to determine if a given transaction is a > withdrawal or deposit to the subaccount. > > I have created the following two functions to implement this: > > (defun sbr/org-lookup-trans (category date s-name r-name) > (let* ((table (concat "trans-" date)) > (s-range (format "@I%s..@>%s" s-name s-name)) > (s-col (org-table-get-remote table s-range)) > (r-range (format "@I%s..@>%s" r-name r-name)) > (r-col (org-table-get-remote table r-range))) > (org-lookup-all category s-col r-col))) > > (defun sbr/get-net-subacct (category date) > (let* ((from-vec (sbr/org-lookup-trans category date "$Subacct" "$From")) > (to-vec (sbr/org-lookup-trans category date "$Subacct" "$To")) > (amount-vec (sbr/org-lookup-trans category date "$Subacct" > "$Amount")) > (from-inbox-ind "Inbox">) ; A > (to-inbox-ind "Inbox">) ; B > (adds amount-vec[from-inbox-ind]) ; C > (subs amount-vec[to-inbox-ind])) ; D > (calc-eval (if (zerop (length subs)) > adds > (concat adds "-" subs))))) > > I need help filling in the pseudo code in lines A through D, namely: > > 1. how to get a list of all indices of elements in a vector that satisfy a > predicate? > 2. how to reference a vector by a list of indices? > > In this example the function should return the following values: > > (sbr/get-net-subacct "Bond" "150925") ==> 150 > (sbr/get-net-subacct "Furnishings" "150925") ==> -75 > (sbr/get-net-subacct "Baby Gifts" "150925") ==> -120 > > How do I implement the above pseudocode functions? Is there a simpler way > to accomplish this task? > --001a113ce1bcdadbe20521d98f34 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Sorry forgot to put in a subject

On Sun, Oct 11, 2015 at 12:51 PM, Shankar R= ao <shankar.rao@gmail.com> wrote:
I h= ave multiple savings accounts, some of which I want to partition into mutip= le virtual "subaccounts" that don't merit their own savings a= ccount. I want to use org-mode tables to keep track how much money is each = subaccount. Given the following table:

= #+TBLNAME: trans-150925
|---+--------+------------+------------+-------------|
| =C2=A0 | =C2=A0 =C2=A0 50 | Short T= erm | Inbox =C2=A0 =C2=A0 =C2=A0| Bond =C2=A0 =C2=A0 =C2=A0 =C2=A0|<= /div>
| =C2=A0 | =C2=A0 =C2=A0200 |= Inbox =C2=A0 =C2=A0 =C2=A0| Short Term | Bond =C2=A0 =C2=A0 =C2=A0 =C2=A0|=
| =C2=A0 | =C2=A0 =C2= =A0120 | Gifts =C2=A0 =C2=A0 =C2=A0| Inbox =C2=A0 =C2=A0 =C2=A0| Baby Gifts= =C2=A0|
| =C2=A0 | = =C2=A0 =C2=A0 75 | Short Term | Inbox =C2=A0 =C2=A0 =C2=A0| Furnishings |

<= font face=3D"monospace, monospace">In this example, I have accounts "I= nbox", "Short Term", and "Gifts". Of these account= s, "Short Term" has two subaccounts "Bond" and "Fu= rnishings", and "Gifts" has a subaccount "Baby Gifts&qu= ot;. I want to create a function that given a subaccount name, calculates t= he net amount to be withdrawn/deposited to the subaccount. Note that "= Inbox" doesn't have any subaccounts, which I want to use to determ= ine if a given transaction is a withdrawal or deposit to the subaccount.

I have created the following two function= s to implement this:
<= br>
(defun sbr/org-loo= kup-trans (category date s-name r-name)
=C2=A0 (let* ((table (concat "trans-" date))
=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0(s-range (format "@I%s..@>%s" s-name s-name))=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0(s-col (org-table-get-remote table s-range))
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0(r-range (fo= rmat "@I%s..@>%s" r-name r-name))
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0(r-col (org-tab= le-get-remote table r-range)))
=C2=A0 =C2=A0 (org-lookup-all category s-col r-col)))
=

(defun sbr/get-net-subacct (category date)=
=C2=A0 (let* ((from-vec (sbr= /org-lookup-trans category date "$Subacct" "$From"))
=C2=A0 (to-vec (sbr/org-lookup-trans category da= te "$Subacct" "$To"))
=C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0=C2=A0(amount-vec (sbr= /org-lookup-trans category date "$Subacct" "$Amount")) = =C2=A0 =C2=A0
=C2=A0 = =C2=A0 =C2=A0 =C2=A0=C2=A0=C2=A0(from-inbox-ind <indices of all from-vec= elements that equal "Inbox">) ; A
=C2=A0 =C2=A0 =C2=A0 =C2=A0(to-inbox-ind =C2=A0<indices of all to-vec eleme= nts that equal "Inbox">) ; B
=C2=A0 =C2=A0 =C2= =A0 =C2=A0(adds amount-vec[from-inbox-ind]) ; C
<= font face=3D"monospace, monospace">=C2=A0 =C2=A0 =C2=A0 =C2=A0(subs amount-vec[to-inbox-ind])) =C2=A0; D=
=C2=A0 =C2=A0 (calc-e= val (if (zerop (length subs))
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0= adds
=C2=A0(concat adds "-" = subs)))))

<= /div>
I need help filling in the ps= eudo code in lines A through D, namely:

1. how to get a list of all indices of elements in a vector that satisfy a= predicate?
2. how to = reference a vector by a list of indices?


(sbr/get-net-subacct "Bond" "150= 925") =3D=3D> 150
(sbr/get-net-subacct "Furnishings" "150925") =3D=3D= > -75
(sbr/get-net-= subacct "Baby Gifts" "150925") =3D=3D> -120

How do I implement the above pseudocode functio= ns? Is there a simpler way to accomplish this task?

--001a113ce1bcdadbe20521d98f34--