Querying Date Range from YAML Field - Dataview

Things I have tried

I have looked in the Dataview Github and have scoured the Obsidian forum. I have Googled my problem too and have not found any help. I have checked to make sure my query language is correct many times as well. I have spent quite a couple hours trying.

Here is the query I have tried and can get results with if I remove the WHERE line. When I include this WHERE line, I get 0 results from the query.

TABLE WITHOUT ID file.name AS "Deal Name" , Close_Date AS "Close Date" , Deal_Stage AS "Stage" , Amount , Next_Action AS "Next Action"
FROM "All Deals"
WHERE Close_Date >=date(2022-04-01) AND Close_Date <=date(2022-05-01)
SORT file.name ASC

What I’m trying to do

I am trying to query in a certain folder and use the WHERE command to get only those in which Close_Date is within a certain range.

Here is an example of the frontmatter that should meet the criteria of the query from my perspective:


Deal_ID: [“0000000000”]
Deal_Name: [“xxxxxxxxx”]
Company_Name: [“xxxxx”]
Next_Action: [“3/25/22- Working on it.”]
Deal_Stage: [“xxxxxxxxxx”]
Close_Date: [“2022-04-30 12:28”]
Deal_owner: [“wbaker”]
Amount: [“999999.0”]

Dataview Settings Date Format - yyyy-MM-dd
Dataview Settings Date + Time Format - yyyy-MM-dd hh:mm

Hi.
About your problem, you have many issues with your frontmatter fields. All invalid values…
I don’t know where to start…

  1. Why you use square brackets - [ ... ] - in your fields?
    You can use square brackets to multiple values - a list -, but for single values that isn’t necessary and complicates your dataview queries - because you can’t query a single value but an array/list (for example, you never can’t use WHERE Deal_Name = "xxxxxxxxx" but need to use always WHERE contains(Deal_Name, "xxxxxxxxx").
---
Deal_ID: 0000000000
Deal_Name: xxxxxxxxx
Company_Name: xxxxx
Next_Action: 3/25/22 - Working on it
Deal_Stage: xxxxxxxxxx
Close_Date: 2022-04-30T12:28
Deal_owner: wbaker
Amount: 999999.0
Field_A: ["value 1", "value 2", "value 3"]
Field_B:
  - value B1
  - value B2
  - value B3
---
  1. For multiples values (a list) use one of the options above in Field_A and Field_B

  2. You need to use straight quotes ("), not curly quotes ().

  3. In frontmatter, quotes are used for strings, links… For numbers and dates/time you don’t need to use quotes.

  4. Formats defined in dataview settings are formats for the output (how you “view” a date in your query results), not the format how you need to write your data. For dates you need to use the format YYYY-MM-DD (e.g 2022-04-07); for timestamp you need to use the format YYYY-MM-DDTHH:mm (e.g 2022-04-07T19:23)
    Data Annotation - Dataview

For now clarify the right way and the right syntax to write your data… and then we can talk about the dataview query.

2 Likes

Ok. Thanks for all the help. This frontmatter was exported by a python script from a csv file which automatically added the quotes and square brackets. I will follow up on all the things you mentioned and get back to you. I really appreciate all the detail.

@mnvwvnm That solution worked quite well. Thanks a ton! The query now works and I learned about proper syntax. Much appreciated.

1 Like

Hello, @mnvwvnm

The YAML sample above, includes:

---
Next_Action: 3/25/22 - Working on it
Field_A: ["value 1", "value 2", "value 3"]
Field_B:
  - value B1 
  - value B2
  - value B3
---

In the online help vault, the example YAML for aliases is:

---
aliases: [AI, Artificial Intelligence]
---
  1. Why are quotes used in Field_A but not in Field_B?
  2. Why are quotes used in Field_A but not in Next_Action?
  3. Why are quotes used in Field_A but not in aliases?

Thanks

Angel

EDIT:

And what if people use inline YAML?

aliases:: [AI, Artificial Intelligence]

or

aliases:: ["AI", "Artificial Intelligence"]

Thanks, again.

Hi.
Well, I can answer to your questions without a secure technical language, only by deductions given by the practical experience. :slight_smile:

This one is easy. For generic fields (we’ll see bellow some exceptions for Obsidian yaml rules) we have two options to input multiple values:

  1. For strings, if we use the list format (as in Field_B), we don’t need to use quotes because there’s no confusion possible - each expression (simple or complex) is only one value (a full line value). You can write This is a value with commas and other punctuation, i.e., a single value. There’s no risk to a misreading of the commas as an element to separate multiple values
  2. If, on contrary, we want to create a list of values in a single line, we need to use square brackets (to identify the elements as a list) and add quotes to delimitate each value. We can write ["This is a single value, an expression with commas", "This is a second value"] as in Field_A.

Next_Action is a single value, don’t need quotes.

Now we enter in the exceptions to the rules. There are some special fields in Obsidian: links, aliases, tags…
For this explanation, we can take the cases of the tags and the aliases. In yaml frontmatter (only in this kind of metadata input) we can write tag: tag1, tag2 or tags: tag3, tag4… or alias: title 1, title 2 or aliases: title 3, title 4.
We can check some particularities: by default (no matter if only one value) tags or aliases are considered an array (that’s why we don’t need the square brackets); we can write in singular (tag, alias) or in plural (tags or aliases) and Obsidian read them in the same way; automatically Obsidian read commas as a separation between different values. (Note: we are talking about implicit fields, i.e., a special fields with a special treatment…)

Outside the frontmatter, these fields don’t have a special treatment, they’re considered as a normal custom fields.

  1. To start, aliases:: have the same meaning of key::, i.e, isn’t read by Obsidian as a special metadata (isn’t read by Obsidian as a metadata at all, only by Dataview).
  2. In these type of inline fields (metadata for dataview, not for Obsidian), square brackets don’t mean anything for list purposes. The only valid way to insert multiple values (with some exceptions, as links) is with the use of the quotes:
inline:: "value 1", "value 2"
1 Like

A fantastically comprehensive and clear explanation. Think the help vault would benefit from this level of detail.

Very grateful to you for straightening out some kinks in my limited knowledge.

Thank you.

:clap:

Angel :angel:

1 Like

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