Dataview GROUP BY failing for one file in table

[physicsflute]

What I’m trying to do

I am trying to create a dataview table that groups all notes that have a field showdate::[[date]], in order to make a personal calendar (when I get it all grouped right, I plan to use Mimimal theme cards in 7 columns to make it Sun-Sat)

Some of my dates are recurring, and have non-existent files with the year of 1098. For example, a birthday on Jan 4th would occur every year, and thus a birthday file would contain showdate::[[1098-01-04]]. All other files have the actual year. I have included code to switch out the false year with the current year. This link date format is what I use for my daily notes, so once a daily note is created, I have to delete out the “Daily/” from the link in order for it to be used as a date. All this I have working correctly.

My issue is that, even though my code seems to be working right, there is one file that does not group with the other files with that date, which throws off my Sun-Sat card-view.

Things I have tried

I have included in my table columns for Seeday, typeof(Seeday) and what the dateformat code is putting out, to make sure that they are giving what I want, and they are. These columns I would remove if I could get the grouping working correctly…they are just for double checking my code.

I have tried grouping by the code for the date-format I want it to show: dateformat(date(truncate(replace(Seeday,“[[”, “”), 10, “”)), “MMM d”)

This groups it correctly, with only one Jan 5 entry, but is not in date order but number order, since the output is a string without a year, and that does not help me with my objective.

I can’t figure out why the group by function would be failing for just the one file. Please help!

[holroy]

Might I ask why you’re using [[2023-12-24]] as your date format? And not either 2023-12-24 or "[[2023-12-24]]". The latter would also populate the backlinks pane in the more recent version of Obsidian.

And whilst on that topic, why do you do that truncate/replace thingy? Wouldn’t it be easier and faster to just do substring() (or regexmatch()) ? Not to mention if you insist on that double array date definition, you could get the actual string value through doing view[0][0] (that is if I read your code correctly…)

@physicsflute

I have included in my table columns for Seeday, typeof(Seeday) and what the dateformat code is putting out, to make sure that they are giving what I want, and they are. These columns I would remove if I could get the grouping working correctly…they are just for double checking my code.

When it doesn’t group correctly, most likely the key value is not identical. It’s hard to tell with the images you’ve given (and no actual markdown test files), but you try adding a few extra intermediate columns to help us determine what’s happening:

• length(Seeday) – Just to check that there isn’t any extra characters somewhere
• meta(Seeday) – To show all the meta information on that link, to see if it holds something causing the grouping to fail.

Lastly, one trick I’ve used now and then to make sorting and visual appearance go together when grouping, is using date formats which produces text like: %% 2024-01-05 %% Jan 5. Now the sorting would use the commented full date, but visually you would only see the Jan 5.

[physicsflute]

WOW holroy!

Thank you so much for your quick and so very helpful response.

@holroy

Might I ask why you’re using [[2023-12-24]] as your date format? And not either 2023-12-24 or "[[2023-12-24]]". The latter would also populate the backlinks pane in the more recent version of Obsidian.

I have been using the link format because of the backlinks pane. I didn’t realize that the version with quotes would still show up in the backlinks. Is there an advantage to using "[[2023-12-24]]"?

@holroy

And whilst on that topic, why do you do that truncate/replace thingy? Wouldn’t it be easier and faster to just do substring() (or regexmatch()) ?

The truncate/replace is trying to get rid of the brackets, and I didn’t use substring() because I didn’t know about that function (but now I do, thank you! - it works perfectly for what I was trying to do) and I didn’t use regexmatch() because I don’t know enough about coding to decipher how to use any “regex” functions.

@holroy

but you try adding a few extra intermediate columns to help us determine what’s happening

This is exactly the help I needed. meta() didn’t work because Seeday is just a string, but length() was the issue. The way I removed the path “Daily/” from created notes left in the extra double brackets, and so it was 4 characters longer than the other ones. I changed the code to use substring for it, and it fixed the issue.

This is my current code:

Table without ID dateformat(date(Seeday), "%%yyyy-MM-dd%% MMM d"), rows.file.link as Event, Seeday
Flatten showdate as view
WHERE (date(view) >= date(2023-12-31) AND date(view) < date(2024-01-28)) 
OR ((date(view) = date(1098-12-31)) OR (date(view) >=  date(1098-01-01) AND date(view) < date(1098-01-28)) AND type != "Optional Memorial")
Flatten choice(date(view).year = 1098, substring(replace(string(view), "1098", "2024"),2,12), substring(replace(string(view), "Daily/", ""),2,12)) as Seeday
Group by Seeday

and it works perfectly. I also really appreciate your suggestion of the commented out date format for sorting. I changed my code here to use it, and I can already think of multiple other tables I can “fix” by using that.

The final product!

[holroy]

@physicsflute

@holroy

Might I ask why you’re using [[2023-12-24]] as your date format? And not either 2023-12-24 or "[[2023-12-24]]". The latter would also populate the backlinks pane in the more recent version of Obsidian.

I have been using the link format because of the backlinks pane. I didn’t realize that the version with quotes would still show up in the backlinks. Is there an advantage to using "[[2023-12-24]]"?

If you use just [[2024-01-05]] it’ll not show up in the backlinks, but the quoted version will, "[[2024-01-05]]". And if I’m not mistaken, if you rename a note the quoted version will also be renamed. That’s not true for the unquoted version.

Do try the following in a note of its own, and look at the outlinks pane of that note:

---
alternatives:
- [[Ralph List]]
- Ralph Text
- "[[Ralph Link]]"
---

```dataview
TABLE WITHOUT ID
 alternative, typeof(alternative)
WHERE file = this.file
FLATTEN alternatives as alternative
```

This illustrates how the various alternatives displays, and is treated by Obsidian.

As can be seen in the screenshot doing the unquoted “link format” produces a list of list, and not a link. This can also be seen by the double dot in front of the output, indicating the list of lists. Only the last option is considered a link by Obsidian, and only that one shows up in the outlinks/backlinks…

[physicsflute]

Thanks for this clarifying reply. I understand the difference now. I don’t currently use “Properties”, so all my fields are just added in the body of the note, and there, using showdate::[[2023-12-24]] is the only one that shows up as a straight link. If I use the quotes in a body-text field, it gives it as a string and doesn’t show up in the outlinks.

Maybe now that I know a little more about the differences between properties fields and in-note fields, I might explore them, but so far I have been content without “frontmatter”

[holroy]

My bad, I’ve been answering so many questions on properties lately, that I missed out that you was using inline fields. Inline fields do behave like you say.

[holroy]

Just on a side note, here is a revised query which might work. I say might as I had some issues building a compliant test set, which caused some issues if the showdate was something like [[Daily/2024-01-06]].

```dataview
Table without ID
  dateformat(SeeDay, "%%yyyy-MM-dd%% MMM d"),
  rows.SeeDay as SeeDay,
  rows.file.link as Event
FLATTEN showdate as view
FLATTEN date( 
  substring( replace( replace( string(view),
    "1098", "2024"),
    "Daily/", ""), 2, 12)) as SeeDay
WHERE SeeDay
  AND ( SeeDay >= date(2023-12-31) AND SeeDay <= date(2024-01-28) )
  AND type != "Optional Memorial"
GROUP by SeeDay
```

I’m still presenting this for the following reasons:

• If you use FLATTEN to build the SeeDay as date earlier in the query, you can reuse it later
• In addition if you replace the 1098 with 2024 earlier, you can skip a whole set of date comparisons
• I’m not sure whether your original WHERE query has a correct behaviour with regards to the AND and OR placement, as it doesn’t really make sense when combined with the type != "Optional Memorial" part. So you might want to rethink that in your original query

So sorry for the confusion on the property fields vs inline fields, and hopefully you can learn something from this alternate query (which I’m hoping works in your settings).

[physicsflute]

This revised query works very well in my setup, except for something I hadn’t explained, and thus you wouldn’t know. The strange ANDs and ORs with the “Optional Memorial” do function correctly in my original query. Files that are Optional Memorials have a generic showdate (using the year 1098) because they are specific to a day. However, the years do I celebrate each one varies, and the ones that I celebrate this particular year, I add a specific showdate with 2024. The query as I have it filters out all optional memorials that don’t have the current year as a showdate.

Thanks for continuing to ponder my query. I definitely see the value in defining the date earlier, since it greatly simplifies the “set-up” involved in starting each month. I will continue to experiment with how I can do that, while also keeping the optional memorial bit.