Hi,
I’ve just started playing around with Dataview and I am having problems with thousands separators (comma’s) in numbers.
I have in-line numbers like 1,234 which are read as two separate numbers 1 and 234.
If I make it 1234 it’s all good but it’s hard to read with large numbers. The only way I’ve found to preserve them is using quotes but then the output is not a number and I can’t do any calculations with it. Even if I could format the output correctly that would be a win.
1 Like
Sure. Basically, I have a folder People/Friends & Family where I put my TOC (Table of Contents) file and the single people’s notes.
In my “TOC” note, I have a leaflet map, centered on a theoretical “midpoint” of Germany, so I get a nice view, plus a list of people:
---
tags: friends, family
---
[[+Home]]
---
# Friends & Family TOC
This is the Table of Contents of your friends & family.
```leaflet
height: 500px
lat: 51.133333
long: 10.416667
minZoom: 1
maxZoom: 18
defaultZoom: 5
```
The list is sorted by last modified time, newest at top.
```dataview
table birthday as "Birthday", file.ctime as Created, file.mtime as "Last modified"
from "People/Friends & Family"
where file.name != this.file.name
sort file.mtime descending
```
You can then create some different markers in the settings of Obsidian leaflet (check Docs): I made “Friends”, “Family” and “Location” under Additional Map Markers.
Then simply zoom into the map, right-click and create new markers, specifying the map marker type and the linked note, or (quite cumbersome still)
- export the current leaflet data csv,
- add entries manually (people notes referred to must already exist), and
- import the CSV again.
The CSV basically looks like this (check docs):
People/Friends & Family/+Friends & Family TOC.md/real,Family,51.133333,10.416667,Matthias C. Hormann,,
People/Friends & Family/+Friends & Family TOC.md/real,Friends,51.133333,10.416667,Friend 1,,
People/Friends & Family/+Friends & Family TOC.md/real,Location,52.52134162768218,13.41327381161729,Berlin Weltzeituhr,,3f4981d8-1615-4905-9cc4-e4df122215be
If you leave out the GUID in the last column, it gets created by Leaflet automatically. The locations given here are fake values, of course (except for the “Weltzeituhr” in Berlin).
You may need to refresh the note containing the map after import, to make it show all markers.
My “people” notes are really simple and just have the “calculated” links at top:
---
location: [51.133333,10.416667]
birthday: 1970-07-11
tags: friends
---
[[+Friends & Family TOC]]
`= elink("https://www.google.com/maps/search/?api=1&query=" + this.location[0] + "," + this.location[1], "Google Maps")` `= elink("https://www.google.com/maps/@?api=1&map_action=pano&viewpoint=" + this.location[0] + "," + this.location[1], "Street View")` `= elink("https://www.google.com/maps/dir/?api=1&dir_action=navigate&destination=" + this.location[0] + "," + this.location[1], "Route")`
---
# Friend 1
\* `= this.birthday` (`= (date(today) - this.birthday).years + " Jahre"`)
This will later have the correct links and look like this:
Clicking on the “Route” link, for instance, then opens the Google Route Planner in your browser (from which you could click “Send to my smartphone” and get instant navigation):
Hope that helps!
7 Likes
As of version 0.2.17 the tables are back to normal for me. At version 0.2.14 it was “bugged” and a lot of extra space was between rows in a table.
I like your use of Birthdays. Now I want to add a dataview of upcoming dates on my daily notes. No clue how to write that though. Any dataview ninjas have any ideas?
Before I post as a bug in Github, can someone do a quick test?
in frontmatter I have
- - -
datecreated: 2021-04-27
- - -
In a dataview rule I’m trying to use that field: I’ve tried
list “Film/Film Titles”
where datecreated=2021-04-27
where datecreated=“2021-04-27”
where contains(datecreated, “2021-04-27”)
where contains(datecreated, 2021-04-27)
Keep getting 0 results.
Well, it’s possible but really clumsy due to the restricted syntax of Dataview. I wish we had “real” SQL syntax and/or JS.
Let’s say we want a “upcoming birthdays” table for the next two months from now. Crossing year boundaries must be possible (i.e., if you are in December, you also want the January birthdays), and the list sorted. Also, you want to show what birthday it will be.
Warning: This is not elegant, rather clumsy, relies on numerical computations and lots of repeating calculations! This isn’t production-quality code, but just a proof of concept.
You will have to change the desired duration to show in three places in the dataview code block (look for dur(2 months)).
### Upcoming Birthdays
Here are the upcoming birthdays for the next 2 months (until `=date(today) + dur(2 months)`).
table birthday as "Birthday", choice(
date(today).month*100+date(today).day = birthday.month*100+birthday.day,(date(today)-birthday).year,(date(today)-birthday).year+1) as Turns
from "People/Friends & Family"
where file.name != this.file.name and
choice(date(today).month*100+date(today).day > birthday.month*100+birthday.day,
(date(today).year+1)*10000 + birthday.month*100 + birthday.day,
date(today).year*10000 + birthday.month*100 + birthday.day) <= (date(today) + dur(2 months)).year*10000+(date(today) + dur(2 months)).month*100+(date(today) + dur(2 months)).day
sort choice(
date(today).month*100+date(today).day > birthday.month*100+birthday.day,
(date(today).year+1)*10000 + birthday.month*100 + birthday.day,
date(today).year*10000 + birthday.month*100 + birthday.day)
It will look like this:
Have fun! (And hope for a combined SQL and Javascript engine.)
If anyone knows how to simplify this horrible code (and retain full functionality), please let us know!
5 Likes
Try datecreated= date(2021-04-27)
; )
1 Like
This looks great to me! I presume we need to change the ‘red’ bits for our own purpose? “People/Friends & Family” is a folder, yes?
I am however even less clear what ‘choice’, or ‘date’ should be changed to - hold my hand please!
1 Like
This should work, although I don’t know if you would have to include the square brackets:
where contains(ItemType, "Blog post")
Yep, “People/Friends & Family” is a folder, but you could use any other legal “from” constructs here like “#people” or whatever.
Disregard the “red bits”—this is just forum code formatting and doesn’t mean anything in this case.
The “choice” constructs are mainly to find if a birthday is already in the past for this year, and to construct different values for the numeric addition (it forms numerical pseudo-dates in the form YYYYMMDD and MMDD for the “birthday is in the past” comparisons).
Regarding the date(today): Yes, it calculates from “now”. If you’d need something else as a reference, like maybe the date part from the file’s title (in case of daily notes), you’d have to make careful changes throughout. And test a lot. 
Thanks for trying Lise, it is still not working for me. I think I will take a break from this one now… it is vexing me something chronic.
Scratch that: I got it working. Very weird tho… cutting’n’paste-ing the field: value from one of the original docs made it work (I also jiggled with the formatting). So the issue must have been some stray non-printing character , or some thing (gremlin?)
TABLE Title, ItemType, Year
FROM #zotero
WHERE ItemType=[[Blog post]]
SORT desc
Boom! I got this one working. Many thanks for your work Moonbase59, much appreciated.
2 Likes
I have just discovered this plugin and have been playing with it to create a kind of TOC for my literature notes. It’s awesome.
Currently, my table looks like the picture, and my query is
TABLE author as Author, title as Title, tags as Topic
FROM ""
WHERE contains(file.name, "@") and area = "medieval" and type = "secondary"
sort author asc
(all my lit notes start with “@” in the filename).
I would like to do the following but not sure if it’s possible – does anyone know?
The first, “File” column of the table with the file names is not very informative for my purposes (I name the lit notes by the appropriate bibtex citekey). Is it possible to not have that column at all, and link to the files by one of the other columns, let say, by the title? – This is mostly a cosmetic issue, but then the table would have more space for the other things that are important.
Again, this is a great plugin, I have been using it a lot for all kinds of tables and lists
3 Likes
For removing the first file column, you can add this to your css snippets,
.dataview.table-view-table th:first-of-type,
.dataview.table-view-table td:first-of-type{
display: none;
}
As to having another column link to the file, I don’t know a way yet. The only thing that returns a link from the query is file.link but that still shows the file name which you don’t want.
2 Likes
Has anyone here figured out how to calculate days remaining for an event? I’m trying to have days remaining for a person’s birthday in their respective note and have birthday: 1985-03-03 in my YAML.
I can do = (date(today) - this.birthday) to get the age or = (date(today) - this.birthday).years to get the age in years. I can’t seem to do a countdown till their birthday if it is later this year, or if it’s passed already, then next year.
What I’ve tried so far is to separate the month and date from the calculated full age and for the months I can just do a simple = 12-((date(today) - this.birthday)).months+" months" but how do I do the same for the days since I cannot be sure of the length of a month?
Can’t you do something simpler like this:
table from "People"
where birthday < date(today) + dur(2 months)
Assuming you store birthday like this: birthday: 2021-04-29
1 Like
Be careful with that CSS, because it brute-force-removes the first column of a table.
But what if you had a table like this:
table rows.birthday as Birthday, link(rows.file.name) as Name
from "People/Friends & Family"
group by file.name
The “Birthday” column would be gone!
For @atiz’ use case, it would probably be best to ask the developer to introduce an optional 2nd argument to the link() function, much like he already has for elink().
So one could use something like
link(file.name, author)
in the table.
4 Likes
@SkepticMystic This wouldn’t work for “real” birthdays (with year)—they’d all be in the past.
Otherwise I wouldn’t have had to come up with crazy calculations like in Upcoming Birthdays.
What Dataview lacks is “real” SQL syntax and/or Javascript access (we could use moment.js then, or built-in SQL date calculations).
1 Like