Dataview list files if some field is this and list files one day before

[tacus]

Hello,

In my Daily Notes I have some fields with the food that I eat and my condition. In condition field I add good, ok or bad.

With dataview I tried to list the days of my condition is bad and show the food that I ate. But I would like to list too one day before where my condition is bad.

I tried this:

TABLE Condition, Food, Drinks
FROM "Daily Notes"
WHERE Condition=bad
   AND date(yesterday)

But this query only shows the days where condition is bad. I don’t know how to list the days before where condition is different from bad.

I tried to find some examples on the forum or the help Redirecting... but I couldn’t find some similar examples.

Could you help me please?

Thank you

[mnvwvnm]

Sorry, but is really difficult to understand your goal.

1. You want a list of your daily notes where Condition = "bad".

TABLE Condition, Food, Drinks
FROM "Daily Notes"
WHERE Condition = "bad"

2. You want the to query the values for yesterday? yesterday is a relative date, then if you write in your daily note something related with yesterday, one day later, when you go to the same note, the values changed, because yesterday is related with the present, not a fixed date. (and dataview results are dynamic, not a fixed values)

3. How you define dates in your daily notes? By title, using the YYYY-MM-DD format?

My advice: place here some notes examples, with your metadata, and draw a mockup table with the wanted output result.

[tacus]

Thank you for your answer, here some example of my metadata and the output result that I would like:

file name: 2022-03-22

Food: eggs
Drinks: 5 coffee
Condition: ok

file name: 2022-03-23

Food: fish
Drinks: 1 coffee, 1 coke
Condition: bad

file name: 2022-03-24

Food: chicken
Drinks: 2 coffee
Condition: ok

file name: 2022-03-25

Food: fish
Drinks: 1 beer
Condition: ok

dataview output:

File | Condition | Food | Drinks |

---

2022-03-22 | ok | eggs | 5 coffee |
2022-03-23 | bad | fish | 1 coffee, 1 coke |

Because the condition on day 2022-03-23 it’s bad I would like to see this day to know if some drink or meal affect to my condition, but it’s interesting to see the day before of it because could affect to my condition too.

I tried with yesterday on the query because I don’t know how to do it. How you say doesn’t work.

I don’t know if this experiment will do any good, but I wanted to try it.

I hope you can understand me and help me.

Thank you again

[mnvwvnm]

Hi.
I don’t think the suggested order (“bad day” in one row and “day before” in the next row) is the best.
I suggest this option: «if a bad day, show the day before (link and metadata) on the right columns».
I didn’t explore too much this query, so I don’t know if it works in all wanted scenarios:

TABLE WITHOUT ID file.link AS "**Bad Day**", Condition AS "**Condition**", Food AS "**Food**", Drinks AS "**Drinks**", B AS "(Day Before)", B.condition AS "(DB Condition)", B.food AS "(DB Food)", B.drinks AS "(DB Drinks)"
FROM "Daily Notes"
WHERE condition = "bad"
FLATTEN link(dateformat(file.day - dur(1 day), "yyyy-MM-dd")) as B
SORT file.day DESC

[tacus]

Thanks so much! absolutely better solution, this works perfect.

Now I learn how to do advanced queries with FLATTEN, with my little knowledge and with the Dataview help I couldn’t.

Appreciate your time for help me.

Thank so much again!