On 13/07/24 17:29, Shaarad Dalvi wrote:
Hi folks,
I have a few accounts decorated with some metadata - like for fixed
deposit accounts, I put the rate of interest as the metadata
2024-07-01 open Assets:MyBank:MyFixedDeposit1 INR
rate-of-interest: "7.2%"
... And so on for other deposit accounts
I am wondering if I can somehow see "the fixed deposit accounts that are
giving me the least rate of interest right now"?
beanquery defines a few more tables beside the 'postings' table. For a
list, see the output of the '.tables' command. Among these there is also
the 'accounts' table. For a description of the columns of the table, see
the output of the '.describe accounts' command:
beanquery> .describe accounts
table accounts:
account (str)
open (open)
close (close)
And the output of the '.describe open' command for a definition of the
'open' structured data type:
beanquery> .describe open
structured type open:
meta (metadata)
date (date)
account (str)
currencies (list)
booking (booking)
With this information, you can build this query:
SELECT
account,
open.meta['rate-of-interest']
FROM
#accounts
ORDER BY 2 DESC, 1
I tried using entry_meta and any_meta but no success so far.
As the name suggest, these functions return metadata for the entry and
for the posting or the entry if the former is not present.
Furthermore, if filtering on account metadata is possible, is it
possible to cast the metadata value of a given key to another data type
(like integer/float) and ORDER BY that?
In your example the 'rate-of-interest' metadata field is a string. The
easiest would be to encode it as a decimal, for example:
2024-07-01 open Assets:MyBank:MyFixedDeposit1 INR
rate-of-interest: 7.2
then there is nothing to do to get it interpreted correctly as a decimal
number in beanquery. Otherwise, you need to parse the numerical part out
of the string, which gets a bit clumsy:
SELECT
account,
decimal(substr(str(open.meta['rate-of-interest']), 0, -1)) AS interest
FROM
#accounts
ORDER BY 2 DESC, 1
Metadata values do not have a defined data type in Beancount, thus BQL
(the Beancount Query Language) is treats metadata as a dictionary that
uses strings as keys and untyped object as values. The str() function in
the query above coerces the metdata value to a string. The substr()
extracts the string part before the '%' sign, and decimal() parses the
resulting string as a decimal value (BQL does not have a floating point
data type).
Cheers,
Dan
--
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/bc291af2-88a6-455c-9550-94f5a12a546d%40grinta.net.
