i have a text property “state” with values “1 ToDo” .. “6 Done”
i try to group by week and filter out all states with a value “6 Done” but my script does not work
TABLE WITHOUT ID
week, rows.state, rows.file.link
WHERE !contains(rows.state, 6)
GROUP BY (dateformat(date(Due), "yyyy-WW")) as week
SORT week DESC
Alternatives also not working
WHERE !contains(state, 6)
WHERE !contains(rows.state, "6")
WHERE !contains(state, "6")
Everytime, my results containing entries with “6 Done”
In a local test vault, the following appears to work:
```dataview
TABLE WITHOUT ID
rows.week, rows.state, rows.file.link
WHERE !contains(state, "6")
GROUP BY (dateformat(date(Due), "yyyy-WW")) as week
SORT week DESC
```
Your query was close and had just two things to change.
Since state is a string property and the character “6” is a string within it, your contains needed quotation marks around the 6.
The GROUP BY command creates an array called rows. Before you’ve done any grouping, there is no such thing called “rows” (unless you’ve assigned something AS rows). Your WHERE command came before GROUP BY, so at that point, “rows” didn’t exist, meaning that rows.state was null. So your statement was essentially !contains(null, 6) (“where null doesn’t contain the number 6”), which was always true. So your query showed every note.
Removing “rows” from rows.state (like Guapa showed you) fixes that.
Alternatively, you could move your WHERE !contains(rows.state, "6") to come after the GROUP BY—that would work too. The first option is probably better as it filters first then processes, instead of processing even the things you won’t want to see and then filtering.
If it didn’t work, perhaps the issue is with how the data is stored. What do you see with the sample note below?
---
state:
- 6 Done
week: "7"
---
## q1
```dataview
TABLE WITHOUT ID
rows.week, rows.state, rows.file.link
WHERE !contains(state, "6")
GROUP BY (dateformat(date(Due), "yyyy-WW")) as week
SORT week DESC
```
## q2
```dataview
TABLE WITHOUT ID
rows.week, rows.state, rows.file.link
GROUP BY (dateformat(date(Due), "yyyy-WW")) as week
SORT week DESC
```
Also Guapas code does not filter the state containing “6”
I think, my problem is, that the Property is shown als Text but the Entry is a link/file
it coming from a “metadata menu” class.
Cool. Makes a difference being a link. Should also be able to get part of the link with:
---
state: "[[6 Done]]"
week: "7"
---
## q1
```dataview
TABLE WITHOUT ID
rows.week, rows.state, rows.file.link
WHERE !contains(string(state), "6")
GROUP BY (dateformat(date(Due), "yyyy-WW")) as week
SORT week DESC
```
## q2
```dataview
TABLE WITHOUT ID
rows.week, rows.state, rows.file.link
WHERE contains(string(state), "6")
GROUP BY (dateformat(date(Due), "yyyy-WW")) as week
SORT week DESC
```
