How to filter journal queries by amount ?

[Varac]

Hi,

new beancount user here, I'd like to filter a journal query by amount,
i.e. show all transactions with an amount > X euros.
I tried but nothing seems to work for me i.e.:

"JOURNAL 'Expenses' FROM year = 2014 AND cost_number > '100'"
ERROR: Invalid column name 'cost_number' in FROM clause context.


Greetings, Varac

[Martin Blais]

I think you want "number"

https://github.com/beancount/beancount/blob/v2/beancount/query/query_env.py#L1662

--
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 beancount+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/1603180319.e43z90u5bm.astroid%40rocinante.none.

[Varac]

Can you help me with an example please ?

bean-query ~/beancount/personal.beancount "JOURNAL 'Expenses' FROM number > 800"

didn't work for me.

Greetings, Varac

Excerpts from Martin Blais's message of Oktober 22, 2020 3:20 vormittags:

> To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CAK21%2BhObYOfajfJ5V6kf%3DSjcwdmsEza54%3D7XkeKoGL_gBhXNjQ%40mail.gmail.com.

> CAK21%2BhObYOfajfJ5V6kf%3DSjcwdmsEza54%3D7XkeKoGL_gBhXNjQ%40mail.gmail.com.
>

[Red S]

FROM applies to a transaction, WHERE applies to postings. I may be mistaken, but WHERE cannot be used with JOURNAL for this reason, in my understanding.

This might possibly be what you want:

SELECT date,description,account,position,balance WHERE account ~ "^Expenses" AND number > 800

https://beancount.github.io/docs/beancount_query_language.html

On Thursday, October 22, 2020 at 12:47:32 AM UTC-7, Varac wrote:

Can you help me with an example please ?

    bean-query ~/beancount/personal.beancount "JOURNAL 'Expenses' FROM number > 800"

didn't work for me.

Greetings, Varac

Excerpts from Martin Blais's message of Oktober 22, 2020 3:20 vormittags:
> I think you want "number"
> https://github.com/beancount/beancount/blob/v2/beancount/query/query_env.py#L1662
>
>
> On Tue, Oct 20, 2020 at 3:57 AM Varac <va...@varac.net> wrote:
>
>> Hi,
>>
>> new beancount user here, I'd like to filter a journal query by amount,
>> i.e. show all transactions with an amount > X euros.
>> I tried but nothing seems to work for me i.e.:
>>
>>     "JOURNAL 'Expenses' FROM year = 2014 AND cost_number > '100'"
>>     ERROR: Invalid column name 'cost_number' in FROM clause context.
>>
>>
>> Greetings, Varac
>>
>> --
>> 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 bean...@googlegroups.com.

>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/beancount/1603180319.e43z90u5bm.astroid%40rocinante.none
>> .
>>
>
> --
> 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 bean...@googlegroups.com.

> To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CAK21%2BhObYOfajfJ5V6kf%3DSjcwdmsEza54%3D7XkeKoGL_gBhXNjQ%40mail.gmail.com.
> I think you want "number"
> https://github.com/beancount/beancount/blob/v2/beancount/query/query_env.py#
> L1662
>
>
> On Tue, Oct 20, 2020 at 3:57 AM Varac <va...@varac.net> wrote:
>
>     Hi,
>
>     new beancount user here, I'd like to filter a journal query by amount,
>     i.e. show all transactions with an amount > X euros.
>     I tried but nothing seems to work for me i.e.:
>
>         "JOURNAL 'Expenses' FROM year = 2014 AND cost_number > '100'"
>         ERROR: Invalid column name 'cost_number' in FROM clause context.
>
>
>     Greetings, Varac
>
>     --
>     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 bean...@googlegroups.com.

>     To view this discussion on the web visit https://groups.google.com/d/msgid/
>     beancount/1603180319.e43z90u5bm.astroid%40rocinante.none.
>
> --
> 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 bean...@googlegroups.com.

[Varac]

Thanks, that did it !

Excerpts from Red S's message of Oktober 22, 2020 10:10 vormittags:

> FROM applies to a transaction, WHERE applies to postings. I may be
> mistaken, but WHERE cannot be used with JOURNAL for this reason, in my
> understanding.
>
> This might possibly be what you want:
> SELECT date,description,account,position,balance WHERE account ~
> "^Expenses" AND number > 800
>
> https://beancount.github.io/docs/beancount_query_language.html
>
>
> On Thursday, October 22, 2020 at 12:47:32 AM UTC-7, Varac wrote:
>>
>> Can you help me with an example please ?
>>
>> bean-query ~/beancount/personal.beancount "JOURNAL 'Expenses' FROM
>> number > 800"
>>
>> didn't work for me.
>>
>> Greetings, Varac
>>
>> Excerpts from Martin Blais's message of Oktober 22, 2020 3:20 vormittags:
>> > I think you want "number"
>> >
>> https://github.com/beancount/beancount/blob/v2/beancount/query/query_env.py#L1662
>> >
>> >

>> > On Tue, Oct 20, 2020 at 3:57 AM Varac <va...@varac.net <javascript:>>

>> wrote:
>> >
>> >> Hi,
>> >>
>> >> new beancount user here, I'd like to filter a journal query by amount,
>> >> i.e. show all transactions with an amount > X euros.
>> >> I tried but nothing seems to work for me i.e.:
>> >>
>> >> "JOURNAL 'Expenses' FROM year = 2014 AND cost_number > '100'"
>> >> ERROR: Invalid column name 'cost_number' in FROM clause context.
>> >>
>> >>
>> >> Greetings, Varac
>> >>
>> >> --
>> >> 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 bean...@googlegroups.com <javascript:>.

>> >> To view this discussion on the web visit
>> >>
>> https://groups.google.com/d/msgid/beancount/1603180319.e43z90u5bm.astroid%40rocinante.none
>> >> .
>> >>
>> >
>> > --
>> > 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 bean...@googlegroups.com <javascript:>.

>> > To view this discussion on the web visit
>> https://groups.google.com/d/msgid/beancount/CAK21%2BhObYOfajfJ5V6kf%3DSjcwdmsEza54%3D7XkeKoGL_gBhXNjQ%40mail.gmail.com.
>>
>> > I think you want "number"
>> >
>> https://github.com/beancount/beancount/blob/v2/beancount/query/query_env.py#
>> > L1662
>> >
>> >

>> > On Tue, Oct 20, 2020 at 3:57 AM Varac <va...@varac.net <javascript:>>

>> wrote:
>> >
>> > Hi,
>> >
>> > new beancount user here, I'd like to filter a journal query by
>> amount,
>> > i.e. show all transactions with an amount > X euros.
>> > I tried but nothing seems to work for me i.e.:
>> >
>> > "JOURNAL 'Expenses' FROM year = 2014 AND cost_number > '100'"
>> > ERROR: Invalid column name 'cost_number' in FROM clause context.
>> >
>> >
>> > Greetings, Varac
>> >
>> > --
>> > 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 bean...@googlegroups.com <javascript:>.

>> > To view this discussion on the web visit
>> https://groups.google.com/d/msgid/
>> > beancount/1603180319.e43z90u5bm.astroid%40rocinante.none.
>> >
>> > --
>> > 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 bean...@googlegroups.com <javascript:>.

>> > To view this discussion on the web visit
>> https://groups.google.com/d/msgid/
>> > beancount/
>> > CAK21%2BhObYOfajfJ5V6kf%3DSjcwdmsEza54%3D7XkeKoGL_gBhXNjQ%
>> 40mail.gmail.com.
>> >
>>
>

> --
> 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 beancount+...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/dcb54226-1a9f-4a89-979c-d4ccc905679do%40googlegroups.com.

> FROM applies to a transaction, WHERE applies to postings. I may be mistaken,
> but WHERE cannot be used with JOURNAL for this reason, in my understanding.
>
> This might possibly be what you want:
> SELECT date,description,account,position,balance WHERE account ~ "^Expenses"
> AND number > 800
>
> https://beancount.github.io/docs/beancount_query_language.html
>
>
> On Thursday, October 22, 2020 at 12:47:32 AM UTC-7, Varac wrote:
>
> Can you help me with an example please ?
>
>     bean-query ~/beancount/personal.beancount "JOURNAL 'Expenses' FROM
> number > 800"
>
> didn't work for me.
>
> Greetings, Varac
>
> Excerpts from Martin Blais's message of Oktober 22, 2020 3:20 vormittags:
> > I think you want "number"

> > https://github.com/beancount/beancount/blob/v2/beancount/query/
> query_env.py#L1662
> >
> >

> > I think you want "number"
> > https://github.com/beancount/beancount/blob/v2/beancount/query/
> query_env.py#

> --
> 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 beancount+...@googlegroups.com.

> To view this discussion on the web visit https://groups.google.com/d/msgid/

> beancount/dcb54226-1a9f-4a89-979c-d4ccc905679do%40googlegroups.com.
>

[Martin Blais]

Both can be used, but FROM can be used only with transaction columns and WHERE can be used only with posting columns.

To unsubscribe from this group and stop receiving emails from it, send an email to beancount+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/dcb54226-1a9f-4a89-979c-d4ccc905679do%40googlegroups.com.

[Red S]

beancount> JOURNAL WHERE number > 5
ERROR: Syntax error near 'WHERE' (at 8)
  JOURNAL WHERE number > 5
          ^
beancount> help journal
Select a journal of some subset of postings. This command is a
convenience and converts into an equivalent Select statement, designed
to extract the most sensible list of columns for the register of a list
of entries as a table.

The general form of a JOURNAL statement loosely follows SQL syntax:

   JOURNAL <account-regexp> [FROM_CLAUSE]

See the SELECT query help for more details on the FROM clause.
beancount>

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/dcb54226-1a9f-4a89-979c-d4ccc905679do%40googlegroups.com.

[Red S]

Hit send too soon, sorry. Martin, I assume you meant WHERE can be used with SELECT. Varac was unable to use it with "JOURNAL" (see below), and I'd explained why. Doesn't make a huge difference, but I'd be interested in knowing if I'm missing anything.

[Martin Blais]

Aah yes. Indeed. 

(I hardly ever use that JOURNAL command, so I forget its constraints. I always use SELECT.)

To unsubscribe from this group and stop receiving emails from it, send an email to beancount+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/7813efa0-5411-4292-81ee-42f54b325a91o%40googlegroups.com.