I am trying dataview for the first time, but I have a usecase in mind. I have a folder with a few notes. Each note contains a few properties, lets just say we have name, price and someothervalue. Alright, made a table with those, no big problem.
TABLE WITHOUT ID file.link AS “Item”, name, price, someothervalue
FROM “Dataview Test” and #item
SORT name ASC
What I’m trying to do is have an additional row (preferably at the bottom) with the sum of all the prices. The price would be with a dollar sign at the end, which shouldn’t be a problem thanks to number(price).
Things I have tried
I’ve searched around here a bit and found a few crazy queries with FLATTEN and GROUP BY (don’t really understand FLATTEN, read the docs). None of them have worked.
In the last link they use sum(rows.something). When I try this its not outputting anything in that row at all.
Happy to see you trying Dataview for the first time! It can be a headache. But, I promise you, its worth it! Nonetheless, It can be a bit tricky
Using Dataview DQL
```dataview
TABLE WITHOUT ID file.link AS "Item", name as "name", price as "price", someothervalue as "someothervalue"
FROM "Dataview Test" and #item
```
```dataview
TABLE WITHOUT ID sum(map(rows, (r) => default(r.price, 0))) AS "Total Price"
FROM "Dataview Test"
GROUP BY true
```
Explanation
The first table displays…our table. The second table displays the summary by using GROUP BY to turn all the rows into a singular row that can be added together using sum
Using DataviewJS
let pages = dv
.pages('"Dataview Test" and #item')
let totalPrice = pages.map((page, index) => [
page.file.link,
page.name,
page.someOtherValue,
dv.func.sum(page.price),
]);
totalPrice["values"][totalPrice.length] = [
"***Total***",
"",
"",
dv.func.default(dv.func.sum(pages.price), 0),
];
dv.table(["Item", "Name", "someOtherValue", "Price"], totalPrice);
The script declares the variable pages and uses dv.pages to query all pages that are in the folder “Dataview Test” and have the tag #item
Next, the variable totalPrice is declared and uses pages.map. The map method takes a function as an argument, which is called for each element in the pages array. In this case, the function takes two parameters: page and index.
For each page in the pages array, the function creates an array with four values: page.file.link, page.name, page.someOtherValue, and dv.func.sum(page.price)
page references the individual query results from dv.pages. Using page.file.link we can collect each queries file link. Using page.name we can find its name value (our tag), as well as someOtherValue. Finally, we use dv.func.sum(page.price) to sum all price fields on the page
Afterwards, an array called is created. This array has the values “Total”, two blank values, and dv.func.default(dv.func.sum(pages.price), 0). This array adds predetermined values to totalPrice
dv.func.default(dv.func.sum(pages.price), 0) sums up all price values in all queried pages. It looks very similar to dv.func.sum(page.price) which is why you need to pay extra attention to “page” vs “pages”
Finally, we use dv.table to create four columns to match our array, these columns correspond with the order of the array values. page.file.link was referenced first so it goes with Item, next we have page.name which goes with Name, etc
The totalPrice array is referenced to populate the table
Then…wala! Thats the code explanation
Extra
What is FLATTEN?
Essentially, FLATTEN organizes your results by separating them to their own rows. For example, if Object1 have the values of Value1 and Value2, instead of it being listed as Object1, Value1 Value2 it would be Object1 Value1 and Object1 Value2
Thanks a lot, works like a charm. I also get flatten and group by now. One last question to fully understand the code:
For totalPrice["values"][totalPrice.length].
I guess totalPrice["values"] gets all the values in the array.
And we use totalPrice.length because the length is 2, but the index starts at 0 so we insert a new element after the second (or last in any other case) element in the array right?
