Dataview: Function to get length of unique values OR using nested queries

Help
2

This questions intrigued the programmer in me, and there could possibly be other solution to this more inline with DQL queries. I however changed it into DataviewJS query, and feed your original query into dv.query(), before I then proceeded to present the original result, and post-process the values of that query.

To make the overtime be more than 0 hours, I added another day to the dataset, and I ended up with this result:

image

The code to generate it was this:

```dataviewjs

const dayByDay = await dv.query(`
TABLE WITHOUT ID
	Date,
	round(sum(rows.Hours), 2) AS "Total Hours",
	round(sum(rows.Hours) - 8, 2) AS "Overtime"
FROM "ForumStuff/f48105 Overtime query"
WHERE work
FLATTEN work AS W
FLATTEN file.name AS Date
FLATTEN split(W, "\, ")[0] AS Project
FLATTEN split(W, "\, ")[1] AS Start
FLATTEN split(W, "\, ")[2] AS End
FLATTEN date(Date + "T" + End) AS Tend
FLATTEN date(Date + "T" + Start) AS Tstart
FLATTEN round((Tend - Tstart).hours, 2) AS Hours
GROUP BY Date
`)

// Look at this in Developer Tools
console.log(dayByDay)

if (dayByDay.successful) {
  // Present original table
  dv.table(dayByDay.value.headers, dayByDay.value.values)

  // Calculate extra stuff based upon table 
  let noOfDays = 0
  let totOvertime = 0
  for (let day of dayByDay.value.values) {
    noOfDays += 1
    totOvertime += day[2]
  }
  dv.span(`In ${noOfDays} days you accumulated ${totOvertime} hours of overtime`)
} else {
  dv.span('Day by day query failed')
}
```

The line with console.log(dayByDay) can be removed, but I left in as I used that for debugging within Developer tools to see what was actually returned from the dv.query().

My code does the following:

  1. Execute your original query (with a slightly different from part to accommodate my setup)
  2. Display the debug output
  3. If successful run of the query
    3.1. Display original result using dv.table()
    3.2 Loop over the values of each row in the table, and increase number of days, and accumulate the overtime
    3.3 Display the days and overtime calculations
  4. If not a successful run, just state it

The code is not optimal, and you could for example simply take the length of the dayByDay.value.values array to get number of days, but it shows some of the possibilities available when post-processing a query within DataviewJS.

Hope this helps, at least I had fun doing it!
Holroy

2 Likes