I have a series of notes with different suffixes.
There are mainly three types of file names: ending with " PA" (ie, space then PA, without the quotes), ending with " TS", and ending with " FB".
For example: “My first note PA”, or “My second note FB”.
I need to display a summary table with the different suffixes and their counts, like:
PA 5
TS 2
FB 3
Total 10
Things I have tried
I have tried this:
TABLE "PA" as suffix, length(rows) as number
WHERE contains(file.name, " PA")
group by suffix
Problems:
It only shows one type. I don’t know how to show the rest, or the total. Plus it shows an additional column.
```dataview
TABLE length(rows) AS "file count"
GROUP BY choice(
endswith(file.name, " PA"), "PA", choice(
endswith(file.name, " TS"), "TS", choice(
endswith(file.name, " FB"), "FB", "-"
)
)
) AS suffix
WHERE suffix = "PA" OR suffix = "TS" OR suffix = "FB"
```
And then for the total:
```dataview
LIST without ID "Total " + length(rows)
WHERE endswith(file.name, " PA") OR endswith(file.name, " TS") OR endswith(file.name, " FB")
GROUP BY ""
```
Your WHERE statement was telling Dataview to look at only the PA files; that’s why only those were showing up. This new code includes all three types of files.
And it looks only at the end of the filename (endswith) so that something like “JILL AND PAM.md” won’t accidentally get included.
I did the total count separately because I think you might need dataviewJS to put them in one table, or a br in the TABLE statement. Two queries seemed better than one in this case.
```dataview
TABLE length(rows) as Count
FROM "Scratch"
FLATTEN regexreplace(file.name, "^.* (..)$", "$1") as Suffix
WHERE Suffix = "PA"
OR Suffix = "TS"
OR Suffix = "FB"
GROUP BY Suffix
```
The FLATTEN line might be new to you. FLATTEN creates a new column in each row containing the result of the expression; in this case, pulling out the last two letters of the file name. Then the GROUP BY command rolls the rows up by suffix, and the length() function counts them.
Maybe this will be a useful starting point for you. Hope it helps!
Do you have any other files, except those with the suffixes? Can you somehow differentiate these files using some other means, or do we need to actually match against the extracted suffix?