Problem in dataview query when multiple conditions in one page/note

Mayank2 · May 15, 2022, 7:01pm

I tried all the mention steps in this post: Easiest way to implement "days since" or "days till" counters?

I am trying to query via due date in all the vault. But its not working if there are multiple due dates in one note

Mayank2 · May 15, 2022, 8:03pm

Alternatively - Instead of File as the first section in the table can i have all the headings containing duedate in each of them in one note?

Example i have a note called subscription and there are due dates under heading. Now in this situation if i have this combo of heading + due date anywhere in the vault in any note how to show them all in one dataview table. I dont want th note name to be shown but the heading to be shown in the table

mnvwvnm · May 15, 2022, 9:02pm

Hi.

Besides other posts about the matter, have you read the plugin documentation?
Do you know what is metadata for dataview?
Do you know how metadata are structured in each note?

Why I ask? Because sometimes we need to know the minimum about the “tool” to know how we can build the notes (and not the inverse).

Don’t get me wrong, but in a fast answer I could say: “no, you can’t achieve what you want”. Without other explanation.

But avoiding a “dry” answer , I can point some issues:

Headings - For now dataview doesn’t consider headings (sections) as metadata (only if you work with tasks). But there’s some news on the way about sections (because there’s a beta version in background with new features and the sections are in the roadmap)… but I don’t know how and when.
duedate - duedate:: is your custom field with multiple entries, i.e., it’s an array/list. Something like:

duedate:
  - 2022-10-20
  - 2022-09-20
  - 2022-08-20

There’s no relation between each value and any pseudo-group of metadata you can create near your field. For example, if you create this:

## Group 1
duedate:: 2022-10-20
place:: Berlin

## Group 2
duedate:: 2022-09-20
place:: Paris

## Group 3
duedate:: 2022-08-20
place:: Lisbon

… there’s no relation between duedate and local in each fictitious group! Metadata are

duedate:
  - 2022-10-20
  - 2022-09-20
  - 2022-08-20

place:
  - Berlin
  - Paris
  - Lisbon

These are fields related with the page/note, not a small groups of metadata. Because this, any apparent order is just that, an fictitious order. If you give the command to sort or flatten one of the fields you see all the order destroyed.

I could suggest some approaches but, with the new version on the way, I don’t know if it makes sense. (you can always create a new note for each group of metadata)

mnvwvnm · May 15, 2022, 9:06pm

An extra…

Go to settings > dataview and enable all types of queries.
In one note with some of your custom fields, write this inline query (just copy-paste):

`$=dv.span(dv.current())`

Now go to reading mode and see all metadata available for that note and the structure…

Mayank2 · May 15, 2022, 11:40pm

Thanks @mnvwvnm I will check the suggestions given by you. Yes i am new to the tool and would need to go over the documentation in details.

Below is the one i tried initially and its giving me 0 results

Similar structure i have in different different notes. And i was looking to collate all those records having duedate and show in one table. But it doesnt work. May be as you mentioned it doesnt work with headings as of now

mnvwvnm · May 15, 2022, 11:48pm

Going directly to your query, you can’t use duedate - date(today) if duedate is an array! That’s no possible to calculate a list (two values) minus a date: need be a “one date - one date”. (for duedate you have two values…i.e. a list of values, an array).
How dataview choose one? What’s the valid one? You need to give precise instructions to a code … there’s no artificial intelligence here.

Maybe with the new release you can use something like a list under a heading (because lists becomes metadata with a reference to the section):


# Heading 1
- duedate:: 2022-12-20
- otherfield:: project A

# Heading 2
- duedate:: 2022-11-27
- otherfield:: project B

Mayank2 · May 18, 2022, 9:34pm

Thanks @mnvwvnm. I am checking this solution.
I am also going thru the docs.

What i am trying to build here is a table showing my upcoming subscription details.
So the table should have 2 columns: service, ending date

Netflix, 2022-05-20
Disney, 2022-09-09 etc etc

And while in table it should not show the actual date. but should show like
Netflix, in 2 days
Disney, in 3 months, 4 days

something like this. This format is available in dynalist. But we dont have table there.

mnvwvnm · May 18, 2022, 10:10pm

In a faster answer, I suggest something like this:

Place where you want (any note in your vault) the field (or multiple fields) subscription. For example:

Note 1

subscription:: Netflix | 2022-05-20

subscription:: Disney | 2022-09-09

Note 2

subscription:: HBO | 2022-10-31

You need to follow this rule: field:: service | ending date
Now, let’s try a query:

TABLE WITHOUT ID
	split(sub, "\|")[0] AS "Service",
	date(split(sub, "\| ")[1]) - date(today) AS "Remaining time to end"
WHERE subscription
FLATTEN subscription AS sub
SORT date(split(sub, "\| ")[1]) ASC

Let me know if it works.

Mayank2 · May 18, 2022, 11:48pm

Absolutely Brilliant. Thanks a lot @mnvwvnm . it worked…

Twita · May 19, 2022, 2:49am

Quick question - as a newbie, I’m trying to replicate with a different example and getting stuck.

I’m trying to track companies and their rev:
Revenue:: Coke | 3B
Revenue:: Pepsi | 1B

I think I’m using the ‘sub’ function wrong ? I know this is simple but would really appreciate a hand…thanks !! (loving this product and forum!)

Twita · May 19, 2022, 3:19am

OK - i’m an idiot - figured out. Thought sub might be related to ‘subscription’ but it’s not. I’m good - thanks !

mnvwvnm · May 19, 2022, 8:14am

sub isn’t a function… is just a new name to the flattened subscriptions values.

FLATTEN subscription AS sub

“subscription AS sub” rename subscription as sub (is optional, could be only FLATTEN subscription). From this moment (because the order matters) subscription field is replaced by sub.

Twita · May 19, 2022, 4:01pm

I’m confused by the ‘order matters’ comment, as the term ‘sub’ appears prior to the ‘FLATTEN subscription AS sub’ command…

Also - in the example you successfully associate TWO values (separated by |). Would it be possible to associate THREE values ?

Thanks !

mnvwvnm · May 19, 2022, 4:18pm

About the order, the column declarations (TABLE file.name, file.tags) doesn’t count. In reality, this line is the last thing to be considered - is related to the output)
If you add FROM, this is related with the source (doesn’t count either).
From now on order matters: the order of commands as where, flatten, sort…
About the number of values, you can use how many you want:

split(field, "\|") - split the initial value in the number of strings remaining after the split. So, if the value in field is value 1 | value 2 | value 3 | value 4 you get as result a list of values (value 1, value 2, value 3, value 4).
now, if you want only the first value you need to add the [0]: split(field, "\|")[0]
if the second you need to change to [1]: split(field, "\|")[1]
and so on…

Twita · May 19, 2022, 9:56pm

That’s incredibly helpful. Thanks a million…