What I’m trying to do
In my daily journals, I have a frontmatter list-type property named locations that I enter in where I visited that day. Most days I am in one location, but I have several days where I am in different locations during the course of a day. I am trying to create a summary view that lists where I was located each month.
What I would like the Dataview query output to look like is:
| Month | Locations |
|---|---|
| 2025-01 | Seattle |
| 2024-12 | Seattle |
| San Francisco | |
| New York City | |
| 2024-11 | Seattle |
| Portland | |
| 2024-10 | Seattle |
Each daily journal has frontmatter properties like date and month that I can query.
---
tags:
- daily
date: 2025-01-06
year: 2025
month: 2025-01
week: 2025-W02
day: Mon
locations:
- Seattle
---
Things I have tried
I am close to getting what I want, but I have a lot of duplicate rows and would like help finding ways to not have duplicate rows.
My dataview query:
TABLE WITHOUT ID
link(months) AS When,
location AS Location
FROM #daily and -"Templates"
WHERE locations
GROUP BY [month, locations]
FLATTEN dateformat(key[0], "yyyy-MM") AS months
FLATTEN key[1] as location
SORT key[0] DESC
Output:
| When | Location |
|---|---|
| 2025-01 | Seattle |
| 2025-01 | San Francisco |
| 2025-01 | San Francisco |
| 2025-01 | New York City |
| 2025-01 | Seattle |
| 2024-12 | Portland |
| 2024-12 | Portland |
How do I get rid of duplicate rows and also show 1 month entry for multiple locations? Thanks in advance! I’ve been searching these forums and Reddit for days now and have gotten close!