I have data in the following format in a single note:
Section3
Size:: Project1 - 300
Section4
Size:: Project2 - 200
Size:: Project3 - 291
Section5
Size:: Project4 - 288
Size:: Project5 - 392
Size:: Project6 - 164
I’m trying to get a table that lists the projects and the numbers after the dashes and finally as the last row, the total of the numbers after the dashes. The following works if the project headings don’t have any numbers in them, but unfortunately they can be quite complex and contain a mixture of numbers and characters.
TABLE WITHOUT ID split(Size, " - ")[0] AS Project, choice(contains(Size, "TOTAL"), "**" + sum(number(this.Size)), number(split(Size, " - ")[1])) AS Size
FROM "Notes"
FLATTEN Size
WHERE file.name = this.file.name
TOTAL: =round(sum(number(this.Size)), 2)
Any ideas?
Alternative solutions to creata a table with e.g. two columns that can sum the numbers in a column are welcome, preferably without the advanced tables plugin.
This is a variant of summing an arbitrary column which have been answered some times lately in this forum. See thread below for a comprehensive alternative list to this issue:
This variant doesn’t have a pure query to get the needed value, so in order to address the issue of matching against your particular setup I opted for using split() with a regex delimiter, where it stores the alternatives. This resulted in the following base query:
```dataview
TABLE WITHOUT ID proj[1] as Project, proj[2] as "Some number"
FLATTEN file.lists as item
WHERE item.Size
FLATTEN list(split(item.Size, "^(.*?) - ([^-]+)$")) as proj
WHERE file = this.file
```
The regex used splits the value into two segments, one everything else except the last dash followed by a text with no dashes (aka the number at the end). These two matching groups are stored into proj, where proj[0] is everything, proj[1] is the project name and proj[2] is the number.
Combined into a dataviewjs where the last part is more or less boilerplate from the thread above:
```dataviewjs
const result = await dv.query(`
TABLE WITHOUT ID proj[1] as Project, proj[2] as "Some number"
FLATTEN file.lists as item
WHERE item.Size
FLATTEN list(split(item.Size, "^(.*?) - ([^-]+)$")) as proj
WHERE file = this.file
`)
if ( result.successful ) {
const values = result.value.values
// Find the sum of all
const sum = values.map(m => parseInt(m[1])).reduce((a,c) => a + c, 0)
result.value.values.push(["<span style='float: right; font-weight: 750'>Total</span>", sum])
dv.table(result.value.headers, result.value.values)
} else
dv.paragraph("~~~~\n" + result.error + "\n~~~~")
```
