topic Re: Aggr DATA : Zero when no record for specific field value in App Development

Aggr DATA : Zero when no record for specific field value

Jeffx — Fri, 06 Jan 2023 13:30:40 GMT

I have the following data :

And I would like get this table where all possible field values for Year are listed :
[Possible values for Year field are 2020, 2021, 2022]

Note that there are no records for the Year 2021 in the database, how can I achieve this ?

I tried using the "Aggr" function, but the output does not contain any line for 2021.

Aggr(Sum(Qty), Item, Year)

KGalloway — Fri, 06 Jan 2023 21:45:03 GMT

If 2021 does not exist in the data, it will not appear in any aggregations.

You could put in some "dummy records" for missing years (like 2021).

For example:

Item Year Total

42.1234 2020 10

42.1234 2021 -

42.1234 2022 40

KGalloway — Fri, 06 Jan 2023 21:46:32 GMT

Sorry! Clicked reply before I added the screenshot:

Jeffx — Mon, 09 Jan 2023 08:12:32 GMT

Thanks, that's what I thought. Unfortunately I do not have access to the database, how can I add data to an existing table ?

KGalloway — Mon, 09 Jan 2023 14:03:18 GMT

If you have access to the load script of the app, you could add it there by loading the table and then concatenating the "dummy records".