From Newsgroup: comp.sys.acorn.apps

I have a fairly basic Pipedream custom function that adds up the total
of all the items that match an arbitrary list of categories (items of expenditure that come under the heading of 'food'). But it gives an
error if any of the categories in the list are not found in the input,
because the lookup function fails.

Typical usage:
edibles(array1,array2), where array 1 and array2 are non-consecutive
database columns containing categories of expenditure (e.g. Transport,
Grocery, Clothes) and totals spent under those categories. I just want
to be able to add up the subtotal of the amount spent on edible items specifically.

But how do I 'blank' the input so that a non-existent category is
counted as having a value of zero, rather than throwing an error?
Currently I have to manually add an extra row to the input arrays, e.g. "Dairy", 0, in order to suppress the error when in occurs.

I feel that it ought to be possible to have some kind of 'if' option
that would return zero if the lookup fails, but I can't work out a way
of doing it.)

=================================

Add up the total value of all the entries in array 2 which correspond to entries in array 1 that match one of the text values specified in this function. ------------------------------------------------------------------------ ...function("edibles","categories:array","prices:array") ...lookup(C7,@categories,@prices) Dairy ...lookup(C8,@categories,@prices) Greengrocery ...lookup(C9,@categories,@prices) Grocery ...lookup(C10,@categories,@prices) Meat
...result(sum(A7A10))
--
Harriet Bazley == Loyaulte me lie ==

It is better to have loved and lost than just to have lost.
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.sys.acorn.apps

In article <50d0090c5c.harriet@bazleyfamily.co.uk>,
Harriet Bazley <harriet@bazleyfamily.co.uk> wrote:

I have a fairly basic Pipedream custom function that adds up the total
of all the items that match an arbitrary list of categories (items of expenditure that come under the heading of 'food'). But it gives an
error if any of the categories in the list are not found in the input, because the lookup function fails.

Typical usage:
edibles(array1,array2), where array 1 and array2 are non-consecutive
database columns containing categories of expenditure (e.g. Transport, Grocery, Clothes) and totals spent under those categories. I just want
to be able to add up the subtotal of the amount spent on edible items specifically.

But how do I 'blank' the input so that a non-existent category is
counted as having a value of zero, rather than throwing an error?
Currently I have to manually add an extra row to the input arrays, e.g. "Dairy", 0, in order to suppress the error when in occurs.

I feel that it ought to be possible to have some kind of 'if' option
that would return zero if the lookup fails, but I can't work out a way
of doing it.)

=================================

Add up the total value of all the entries in array 2 which correspond to entries in array 1 that match one of the text values specified in this function. ------------------------------------------------------------------------ ...function("edibles","categories:array","prices:array") ...lookup(C7,@categories,@prices) Dairy ...lookup(C8,@categories,@prices) Greengrocery ...lookup(C9,@categories,@prices) Grocery ...lookup(C10,@categories,@prices) Meat
...result(sum(A7A10))

I think you need to use dsum()
--
------------------------------------------------------------------
Richard Torrens.
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.sys.acorn.apps

On 13 Apr 2025 as I do recall,
Richard Torrens (News) wrote:

In article <50d0090c5c.harriet@bazleyfamily.co.uk>,
Harriet Bazley <harriet@bazleyfamily.co.uk> wrote:

[snip]

I feel that it ought to be possible to have some kind of 'if' option
that would return zero if the lookup fails, but I can't work out a way
of doing it.)

=================================

Add up the total value of all the entries in array 2 which correspond to entries in array 1 that match one of the text values specified in this function. ------------------------------------------------------------------------ ...function("edibles","categories:array","prices:array") ...lookup(C7,@categories,@prices) Dairy ...lookup(C8,@categories,@prices) Greengrocery ...lookup(C9,@categories,@prices) Grocery ...lookup(C10,@categories,@prices) Meat
...result(sum(A7A10))

I think you need to use dsum()

Hmmm. dsum() is what is creating these summary blocks in the first
place (listcount to analyse the categories present in a very long list,
then dsum() replicated downwards to add up the values corresponding to
each of those categories).

But I don't see how to use it to look up the numeric value in one array corresponding to the textual value in a parallel array....
All I actually want to do here is to add up an arbitrary subset of the
values, but where I don't know in advance which values are necessarily
going to be present or which rows they are going to be located on -
since that depends on the output of listcount().

So I might have a block that looks like

Dairy 1 0.59
Entertainment 2 61.50
Greengrocery 5 64.95
Grocery 4 17.00
Household 1 5.00
Meat 3 12.60
Music 2 508.76
Postage 1 3.95
Repairs 2 271.00
Transport 1 3.50
Utilities 5 146.27

or one that looks like

Entertainment 1 10.00
Greengrocery 5 81.91
Grocery 3 11.00
Insurance 1 124.09
Meat 2 15.50
Music 2 110.00
Tax 1 226.85
Utilities 5 128.24

but I want to return the sum of the values for however many of Meat,
Dairy, Grocery and Greengrocery happen to be present.
--
Harriet Bazley == Loyaulte me lie ==

Sir Francis Drake circumcised the world with a 100-foot clipper.
--- Synchronet 3.21d-Linux NewsLink 1.2