Flatten in dataviewJS

Things I have tried

I would like to reproduce this dataview query in dataviewJS:

TABLE length(rows.file.link) 
FROM #evergreen 
GROUP BY topic
SORT topic

I have come to this dataviewJs, but I can’t figure out how to flatten the query by topic.

let pages = dv.pages('#evergreen'); 

for (let group of pages.groupBy(b => b.topic)) {
  dv.header(3, group.key);
  dv.table(["Topic", "Note"],
    group.rows
      .sort(k => k.topic, 'desc')
      .map(k => [
        k.topic,
        group.rows.length
      ]
    )
  );
}

After a lot of research i kind of have an idea that is has to be done with something similar to this, but now i’m totaly lost.

.flatMap(page => page.topic)

Can someone kindly point me in the right direction ?
I don’t find any resources on the web which explains how to build tables in dataviewJS.
The dataview help, the dataview showcase, the questions on github all contain information. But everytime i am trying to achieve something i spend a lot of time researching to put the puzzle together.

There’s also this approach i have tried to populate the dv.table but it’s a dead end in so many places:

const rawData = await dv.query('TABLE WITHOUT ID file.name,	G.distanz, G.dauer FROM "10 - Daily Notes" WHERE sport AND contains(sport.type, "Gehen") FLATTEN sport.gehen AS G');
const rows = rawData.value.values;

// and then use 

rows.map(x => x[0])

// somehow to populate an array

Don’t know how to do flatten in dataviewJS, and actually wondered a little bit about that myself recently. However, I do know how to populate a table, and how to produce data for that table.

If you look at Dataview: Function to get length of unique values OR using nested queries - #2 by holroy, it uses dv.query() to build the data set, console.log(dayByDay) to view the structure in the developers tools windows, and finally a call to dv.table(dayByDay.value.headers, dayByDay.value.values) to produce a table based upon the query result.

I reckon going through this script, could serve as a guide as to how to build dedicated tables for population through dv.table().

Hope this helps,
Holroy

PS! Any particular reason you want to rebuild your queries into DataviewJS, if they work just fine in Dataview?

I will take a look, thank you. Any help is appreciated.

I have a lot of free time right now because I am on extended sick leave. I would like to learn much more about dataviewJS. I managed to build some useful templates with it (in combination with Templater).

Being more flexible as normal dataview, i think i can use that as leverage to build some cool stuff in my vault.

So, basically for learning purpose which hopefully will produce useful things.

1 Like

With your example I am nearly there.
I would like to figure out how to add the extra stuff i have calculated based upon table as an extra column into the table. That’s where I am struggling now.

If you use a similar table as the dayByDay from my query, you would need to add a new header, doing something like:

dayByDay.value.headers.push("My header")

And then within the loop of dayByDay.value.values you’d do something similar to add your new values on to the values array.

Maybe something like:

day.push(calculatedNewValue)

And of course you’d need to output the table after your modification of the values. So all in all, here are some code where I added the accumulated overtime as a new column in my previous query:

... Kept the rest of the script, as it was ...

if (dayByDay.successful) {
  // Add the new column
  dayByDay.value.headers.push("Accumulated overtime")
  
  // Calculate extra stuff based upon table 
  let noOfDays = 0
  let totOvertime = 0
  for (let day of dayByDay.value.values) {
    noOfDays += 1
    totOvertime += day[2]
    day.push(totOvertime)  // Add the new value for the last column
  }
  
  // Present modified table with new column
  dv.table(dayByDay.value.headers, dayByDay.value.values)

  dv.span(`In ${noOfDays} days you accumulated ${totOvertime} hours of overtime`)
} else {
  dv.span('Day by day query failed')
}

If you want the column in other places, you need to look into array manipulation with javascript.

Holy universe. Thank you so much !
That works perfectly.

I wanted to post my final code, in case it’s useful for you or anybody else.
Any feedback or suggestion for improvements are welcome.

Goal:
Learn how to manipulate tables in dataview.

Use case
I have multiple notes tagged with “evergreen”. Those notes have a topic in frontmatter.

Example:

---
tags: ['evergreen']
topic: 'Social Psychology'
---

As an excercise, I wanted to created a table containing

  • column 1: name of topic
  • column 2: a bar, representing the percentage (qty for this topic compared to the total number of evergreen notes.

It shows me which topics I mainly write about.

Here’s the output:

And the code: here