Calculate sum of weights in each table row (NOT neural networks)

Help
1

Hello,
asking you guys about some auto-calculation on 2 sets of notes

What I’m trying to do

I’ve got a folder of projects, each a note. Let’s call them P1, P2… P15
I also have a folder of indicators, each a note also. Say they are N1, N2, … N8.
Each indicator has its value, listed as property with a same name. [[N5]].value
Each project has one or several weights, each for one of the values. Currently they are organized as a list of maps in each project note:

valweights:
  N3: 1
  N10: 3

In a totally separate note I’ve got a Dataview table listing all of the projects as rows. I want to have a column, that for each project shows a “total weight” calculated by summing all weights for this project multiplied by the value of corresponding indicator:

total_weight_P4 = ([[P4]].valweights.N3*[[N3]].value + [[P4]].valweights.N10*[[N10]].value )

Things I have tried

I understand pretty clearly how I’d done this in Python - a couple of nested loops, or a matrix-on-vector multiplication if I feel a bit less dumb. But I’m not so keen in DQL, and can’t find the correct way to do this.
I suggest some funny application of “map” ought to do this, but can’t wrap my head over it, and I need this to get running sooner, even if hold together with duct tape until I sit to refactor it.

I’m just setting up the structure of this vault, so I can possibly rebuild how all these values are stored, if solution demands it.
I’m even ready to have the list of values-per-indicator as a precalculated constant inside the Dataview block or page.

Any hints would be appreciated

2

OK, looks like I found a way, even a not too ugly one.
So, I restructured the way I store weights in project notes. “valweights” is now not a list of objects, but just a list of lists.

valweights:
  - - "[[N3]]"
    - 1
  - - "[[N10]]"
    - 3

BTW, is it normal that official Linux Obsidian app can’t parse or edit such nested arrays?

then, in the table, I calculate sum of weights as

round(sum(map(valweights, (x) => x[1]*x[0].value )),1) AS sum_wght

My problem is that I can’t then write sorting like
SORT sum_wght ASC
I have to replicate the whole calculation in this line.

Any advice to optimize this?

3

Sorry for a late reply, but you can use FLATTEN to store intermediate results for use later in the query. So say you had this query:

TABLE sum(a+b) as total
FROM "Somewhere"
SORT sum(a+b)

You could instead write:

TABLE total
FROM "Somewhere"
FLATTEN sum(a+b) as total
SORT total

Remember to place any FLATTEN on some line after the FROM line (if you have any), and before you actually use the intermediate result (in my case total). The first line of TABLE ... will be able to access it, since that’s an exception to the rule.

PS! Are you doing this in dataview or dataviewjs ? I see that I might have assumed dataview, which might be wrong…