That does feel like improvement. At least the query isn’t erroring out now.
Would you be willing to post an example of what a page of your source data looks like? That might help us track down what’s going on here.
That does feel like improvement. At least the query isn’t erroring out now.
Would you be willing to post an example of what a page of your source data looks like? That might help us track down what’s going on here.
Sure, this is the template of my daily note
The highlight is temporary, meaning that I erase it as I input the right data at the end of the day.
Hmm, I thought perhaps the highlight might be the issue, but that gives a different error. I’m not sure what we’re doing differently that it works for me but not for you.
Sorry to interfere, but there is most likely one or more of the minutes
which hold some strange value, so maybe try this query and look for anomalies.
```dataview
TABLE WITHOUT ID min(rows.minutes)
FROM "Archive 🗃️/Journal 📅/Days"
WHERE MONTH = [[2023-Jan]]
WHERE minutes
Group by minutes
```
no interference here
actually I had some entries like “-” changed them to 0 and actually the query provides a different error message
To provide more context, everything work smooth when I query the individual dates without making any sum or average
So now it seems like the sum()
function to generate the Average
generates the error.
I got another trick up my sleeves to detect the anomaly in your data set. Could you please try the following:
```dataviewjs
const result = await dv.query(`
TABLE minutes
FROM "Archive 🗃️/Journal 📅/Days"
WHERE MONTH = [[2023-Jan]]
WHERE minutes
`)
if ( result.successful ) {
const noNumbers = dv.array(result.value.values)
.where( v => typeof(v[1]) != "number")
.map( v => [ v[0], v[1], typeof(v[1]) ] )
dv.table(["File", "field", "type"], noNumbers)
}
else
dv.paragraph("~~~~\n" + result.error + "\n~~~~")
```
With a contrived test set of mine, I got the following output:
Where all my noNumbers
were:
minutes:: "660"
, aka stringminutes::
definitions==660==
, aka highlightedWhat do you get running that query on your data set?
Please excuse me if I make any stupid mistakes because I have no knowledge of the dataviewjs language.
I tried the query on both folders, (A) containing missing and “-” values for “minutes” and (B) containing only actual values ie. numbers or 0
This is the result I’ve got for A
and for B
all this dates contain 0 as a value, ie:
Is this helpful?
It showcases whether you’ve got any faulty data. So you should be able to run the main script on “A” since that didn’t find any non numbers. What was the result whennrunning the main script on “A”?
On “B” however, you need to go through every file in the list and change those entries having the “*” as value for minutes, as thoseb files will trip up the main script. After cleaning up (and getting a “no result” there as well), then what do you get when doing the main script for “B”?
Good catch! I agree with @holroy , it looks like you have some pages where the minutes
value isn’t a number, and that could be what’s causing the script to fail.
thank you @holroy and @Craig appreciate your efforts
I now have a new issue to solve namely, changing all "-"s to zero and all the previous labels that I used for Minutes’ for more than 2 years of daily notes
Do you have any ideas on how I could complete that task without doing it manually, perhaps using a Python script?
This is kind of the culprit, whenever it’s not a number, which the previous queries kind of has led up to. Regarding how to handle it, you’ve got basically two options:
minutes
(in over 2 years of files worth)minutes
from the queryAlternative 1 is of course the political correct way of doing it, but it’s a hassle, to say the least.
Alternative 2 is far easier, but it comes at the cost of potentially hiding some rows, which you kind of would like to include. But given the other queries, let’s go for this version, and it is actually simpler than you’d think it be, as we just need to add typeof(minutes) = "minutes"
to the where clause.
So here is the full query, where it simply ignores all the erroneous minutes
values:
```dataview
TABLE WITHOUT ID Count, Tot, Avg, Min, Max
FROM "Archive 🗃️/Journal 📅/Days"
WHERE MONTH = [[2023-Jan]]
WHERE minutes AND typeof(minutes) = "number"
GROUP BY ""
FLATTEN length(rows.minutes) as Count
FLATTEN sum(rows.minutes) as Tot
FLATTEN Tot / Count as Avg
FLATTEN min(rows.minutes) as Min
FLATTEN max(rows.minutes) as Max
```
Hope this helps, and that the process to get here has been educational.
I hadn’t thought of verifying the type of the field. Good thinking!
I’ll definitely go for Option 2, since I’ve no need to check summary statistics for previous years
It was really beneficial to see the process and how it all worked out, it was also a good reminder of the importance of learning dataviewJS.
As JavaScript is such a huge area of study, can you recommend some good resources for me to learn from? I’m not looking to learn the entire language, just enough to be able to write more powerful dataview queries.
Any advice would be greatly appreciated.
Thank you!
Well, you could take a masters degree in Computer Science, and work with it for a few decades, and stumble across javascript every now and then, and see where that takes you…
Or you could just keep playing around with it, copying code which looks useful, and start tweaking it to your needs. Not sure on which are the better resources for learning javascript today, but there are loads of dataviewjs queries floating around in this forum, which maybe could help you learn more of it.
Darn I really willl have too juggle with it, same way I did with dataview, which isn’t the most effective learning process but…
As someone who loves class-based languages, this makes me sad
It’ll take work, but then again if you ask questions which show what you’ve achieved so far and trying to achieve, and as such indicate that you’ve really tried to get it working, I think you’ll experience that those more familiar with the language will easier tend to help and guide you in the correct direction.
Nothing kills my spirit and helpfulness more than someone just saying: “Hey, can you do this for me?” or “This auto-generated ChatGPT code doesn’t work, how to fix?”. When I see a proper attempt from someone trying to get something done, I’ll more often step in to try and help.
Just as a point of clarification (and please forgive me if you already knew this), Dataview and DataviewJS are two very different but related things.
Everything we’ve discussed in this thread has been about the Dataview query language, which is an SQL-like language specifically for running Dataview queries. It’s (arguably) easier to use and more focused than DataviewJS, and is a great place for people to start learning about structured data in Obsidian.
DataviewJS is an environment for running plain JavaScript in your pages, with access to the Dataview libraries so you can do things like query, filter, etc. It’s more powerful than Dataview, but also far more complex and more time-consuming to learn. It’s a better fit for folks who already know Javascript and want to take advantage of the Dataview libraries.
Having said that, if you’re interested in learning either or both, go for it! But I’d recommend getting familiar with the Dataview query language first before diving into DataviewJS.
This is indeed very useful @Craig, I was assuming that I need to learn dataviewJS in order to take full advantage of dataview.
Surely was feeling a bit intimidated by all the amazing things people can do with dataviewJS, but it’s great to know that I can still take advantage of dataview without the need to learnd dataviewJS.
I think I’ll focus on the functions where I’m still lacking.
Going back to the queries, I have 2 new questions now.
We were summarizing one particular field from the front matter, which is not the only one I’m tracking in the daily note. Let’s say I’m tracking 5 fields, with the previous approach, I’d need to create 5 different tables.
Is there a way to group them within a table? I believe the group by function can help, but this has been really confusing for me to master
Facing a new challenge…
One thing that I track is wake_up and end_by time. I input these in the form of 6:00, 20:53
By using the previous dataviewjs query I figured out that Obsidian read such entries as string. Hence I believe I have to convert them into numbers and then summarize them.
By looking at the Dataview documentation I figured out the number() and split(string) function could help, but honestly no Idea how to integrate them in the query that summarize results
`
`
I’m also open to suggestions on different tools to manage this type of information, but for me nothing beat having everything centralized in one place and data priavacy
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.