I have a markdown note file named “DHG1” that contains a task list, organized by products. The tasks also have numbers associated with them. Here’s what the content looks like:
Products
[[fast]] used [n::4] 2023-10-24
[[fast]] used [n::1] 2023-10-30
[[slow]] used [n::4] 2023-10-24
[[slow]] used [n::1] 2023-10-30
I’ve created a DataView with the following code, which shows individual entries:
TABLE WITHOUT ID
file.link AS "Files", regexreplace(Tasks.text, "^(\S+).*", "$1") AS Products, Tasks.n AS Number, Tasks.scheduled AS "Scheduled Date"
FROM "DHG1.md"
FLATTEN file.tasks AS Tasks
WHERE Tasks.scheduled
SORT file.link ASC, Tasks.text ASC
This DataView produces an output as shown in the attached screenshot.
However, I’m looking for a way to modify the DataView to show the sum of the numbers grouped by the “Products” and “Files” columns. Specifically, I’d like to see an output that looks like this:
Files4
Products
Total Number
DHG1
fast
5
slow
5
Additionally, if I have multiple files, I’d like to know the sum grouped by each file as well.
The first output can be achieved by doing sum() after grouping on a combination of file.link and product, and that query would look something like this:
```dataview
TABLE WITHOUT ID
key[0] as Files,
key[1] as Product,
sum(rows.task.n) as "Total number"
FLATTEN file.tasks as task
FLATTEN regexreplace(task.text, "^(\S+).*", "$1") as product
WHERE file.folder = this.file.folder
WHERE task.scheduled
GROUP BY list(file.link, product) as "Files"
```
In the query above you would need to replace the WHERE file.folder ... with something suiting your case, or remove that line altogether and replace it with a FROM statement to be inserted before the FLATTEN statements.
I’m not quite sure what you would like for the “sum grouped by each file” as well. Using plain DQL query, like this one, you can’t easily do multiple sums across the other grouping. Depending on what you want, you could possibly do it using dataviewjs in addition to a query like the one above, but what should that table look like. Should it list the file with the sum of “fast” and “slow”, or should it list the products with the sum from all the files?
If it were to be done within one query, it would look a little hairy, and in addition it would most likely repeat that final sum in all rows (similar to how the file name is repeated above) as dataview isn’t really good at combining cells within a table.
2023-10-24
