Dataview - implicit grouping by note?

Things I have tried

Grouping by date field

What I’m trying to do

I have a need to log events and report on them. I’m sprinkling in-line fields like this in my notes:

What:: A thing happened
When:: 2022-05-01

And I can successfully write a query to return a nice list of events in date order. However, if I log two events in one note like this:

What:: A thing happened
When:: 2022-05-01
What:: Another thing happened
When:: 2022-06-01

Dataview will always group these two together and not sort in order, i.e. if other notes have event dates that fall between these two events, they won’t sort in between them.

Is there a way around this? I feel like I might have missed something obvious. Maybe it’s just a (not unreasonable) limitation and I’m trying to do something atypical. Thoughts?

If you have two uses of the field in the same note, I believe DV will treat it as a list (similar to how it treats the tags field) and then it is keeping that list together when sorting against other notes. You might want to look into the FLATTEN command. Their example only shows flattening on one field, though so I’m not totally sure how to do it for your case. Maybe someone else does? Paging @Craig as someone who might know!

1 Like

Hi all,

This turned into a long post! The TL;DR is at the bottom.

Thanks for your question. It looks like you’re trying to track events by defining them inline with your notes. I do the same thing to highlight events in histories so I can see all the events in a single timeline. However, I approach it a little differently than you do.

The Problem

I think the problem you’re running into is that you’re defining your What and When as separate fields defined multiple times in the same page. As you guessed, Dataview turns them into lists, but there’s no association between them; that is, Dataview has no idea which When goes with which What. Even if you tried to FLATTEN them, you’d have to play some games with row numbers in order to try keep the times and text together.

Craig’s Solution: Single-Field Events

What I do is combine both both attributes into a single Event field, which always begins with a sortable timestamp and is followed by the text of the event.

For example:

Event:: 2022-06-10 Joe and the party finally reach the Spire
Event:: 2022-06-11 The party is taken out by the Guardian

Using this approach, the event date and text always stay together, and it’s easy to collect and sort events that happen on different pages. Because each event starts with a timestamp, they’re easy to sort in date order. Here’s an example of a regular DQL query that collects all your events in a given folder and turns them into a single, unified timeline (you can see the results of this query at the bottom of the post):

```dataview
TABLE WITHOUT ID 
    split(Event, " ", 1)[0] as Date, 
    regexreplace(Event, "^\S+", "") + " " + file.link as Event
FROM "Notes"
WHERE Event
FLATTEN Event
SORT Event
```

So what’s going on with this query? Let’s look at it a piece at a time and break down what’s going on here.

Get Pages with Events

TABLE WITHOUT ID 
FROM "Notes"
WHERE Event

This part is simple. Grab all notes from the “Notes” folder, and grab only the ones that have at least one Event field.

But we have a problem; the individual events are tucked away as a list inside the Event field of each page. How do we get them out?

FLATTEN

FLATTEN Event

This part’s a little tricky. FLATTEN Event changes how the rows are broken up, changing them from page-based to field-based.

After FLATTEN Event, we now have one row for each Event:

SORT

SORT Event

The SORT re-orders the events by date instead of the order they are on the page. This works because each Event field starts with a sortable timestamp:

Extract Fields

Finally, now that we’ve got one event per row sorted in timestamp order, we can pull the “When” and “What” fields back out of it.

split(Event, " ", 1)[0] as Date, 

This uses the split() function to pull out the text before the first space and put it in a column called “Date”.

regexreplace(Event, "^\S+", "") + " " + file.link as Event

This does three things:

  1. Uses the regexreplace() function to replace the date with an empty string,
  2. appends a link to the file the event appeared in so that the user can click it, and
  3. finall puts the text into a column named “Event”.

Example

Finally, here’s a practical example from my reference vault:

Final Thoughts

This isn’t the only way to do this, of course. You can do some real magic with dataviewjs and turn these Events into lists, group them by date, and more. But I thought this might help you understand what Dataview is doing under the hood and inspire you to even better ideas!

Hope this helps,

Craig

5 Likes

Thanks so much, Craig. You’ve explained this very well - I appreciate you taking the time to go into so much detail. I will go away and have a good think about how I structure the data to fit in with the way Dataview works. I know SQL, so was drawn to the query language, but it may pay for me to go to dataviewjs as I will probably end up there anyway. In case you are wondering, my use case is to build a series bible for a series of fiction novels, so I want to record what, when, where and who for key events in the stories. With what I’ve done so far, Dataview can pull out a nice synopsis and also a kind of character arc for each character. This question came about because I realised I wanted to add additional character notes and have them sort into the event sequence, and my first try attempted to put multiple of these into one Obsidian note, the character sheet. I’ve now got a better idea of how I can make that work, thanks!

1 Like

@Lucid I am just learning about query languages (having done a project in the past that used a nosql database and also spent time thinking about key-value store caches instead of databases). I noticed that SQL doesn’t have “FLATTEN” the way DQL does: does SQL have an equivalent operation or set of operations? Any other conceptual differences (i.e. not talking about how SQL has tons more commands but instead mental translations I should do if reading about SQL and thinking about DQL) you’ve noticed between SQL and DQL? Thanks!

1 Like

I’m far from being an authority on DQL, but there’s pretty much nothing SQL can’t do to manipulate data sets, although some of the syntax can make your head hurt. Based on Craig’s example above, flatten looks just like you’d get from a simple join in SQL. SQL can also easily pivot rows into columns or unpivot columns into rows, maybe also applicable. You could also split strings and turn them into rows, and so on.

1 Like

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.