Thanks Dan. Your script worked pretty well.
On Friday, January 21, 2022 at 10:17:06 AM UTC-8 [email protected] wrote:
> On 21/01/2022 16:05, Maayaas wrote:
> > Thank you. It looks like I need to write a script to do this instead of
> > searching for a BQL recipe.
>
>
> You can easily write a script that post-processes the data returned by a
> BQL query. I often use Petl in combination with BQL for this. A simple
> example solving your issue:
>
> import click
> import petl
> from beancount import loader
> from beancount.query import query
>
> @click.command()
> @click.argument('ledger')
> def main(ledger):
> entries, errors, options = loader.load_file(ledger)
>
> rtypes, rows = query.run_query(entries, options, """
> SELECT
> last(date) as date,
> leaf(account) as account,
> sum(position) as outstanding
> WHERE
> root(account, 2) = "Assets:Receivable"
> GROUP BY leaf(account)
> ORDER BY outstanding DESC
> """)
>
> table = petl.wrap(rows) \
> .pushheader([name for name, rtype in rtypes]) \
> .select('outstanding', lambda x: not x.is_empty())
>
> print(table.lookallstr(style='simple'))
>
> if __name__ == '__main__':
> main()
>
> Cheers,
> Dan
>
>
> > On Friday, January 21, 2022 at 1:38:10 AM UTC-8 [email protected]
> wrote:
> >
> > No way to currently do it AFAIK. A HAVING clause
> > <https://github.com/beancount/beancount/issues/114> is on the wish
> > list. See this comment
> > <
> https://github.com/beancount/beancount/issues/114#issuecomment-632815268>.
>
> >
> > On Thursday, January 20, 2022 at 9:51:06 PM UTC-8 Maayaas wrote:
> >
> > Hi,
> >
> > I am using the query below to get a list of outstanding payments:
> >
> > SELECT
> > last(date), leaf(account), sum(position) as outstanding
> > WHERE
> > account ~ "AccountsReceivable"
> > GROUP BY leaf(account)
> > ORDER BY outstanding DESC
> >
> > It works as intended but it also lists the zero balance
> > accounts. Is there a way to exclude them?
> >
> > Thanks.
> >
> > --
> > You received this message because you are subscribed to the Google
> > Groups "Beancount" group.
> > To unsubscribe from this group and stop receiving emails from it, send
> > an email to [email protected]
> > <mailto:[email protected]>.
> > To view this discussion on the web visit
> >
> https://groups.google.com/d/msgid/beancount/dfbab351-b922-4959-b487-865e9b52fdden%40googlegroups.com
>
> > <
> https://groups.google.com/d/msgid/beancount/dfbab351-b922-4959-b487-865e9b52fdden%40googlegroups.com?utm_medium=email&utm_source=footer>.
>
>
>
>
--
You received this message because you are subscribed to the Google Groups
"Beancount" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/beancount/0c9d8d1b-cd03-4d96-9374-cc62f47488b3n%40googlegroups.com.
