Dataview Table with Count

(1) I have a folder A with files and subfolders with files. All these files have its My_Rating from 1 to 10. I would like a table to list down the number of files with the ratings from 10 to 1. How do I build the table?

(2) Currently, I have one table per rating with this:
TABLE length(rows)
AS count
WHERE My_Rating = 10
GROUP BY Category

But I have column Category with a “-” and then column Count with the count.

(1) How can I have a table with column My_Rating of 10 to 1 and column Count from all files in the folder and sub-folders

(2) For the one table per rating, how can I improve it?

Many thanks in advance!

If I understand you correctly, then the trick to solving your issues to do a GROUP BY on a combination of fields. So try something like the following:

TABLE WITHOUT ID group[0] as Category, group[1] as Folder, catCount as count
WHERE My_Rating = 10 AND Category
GROUP BY list(file.folder, Category) as group
FLATTEN length(rows) as catCount
SORT catCount DESC

Doing the AND Category eliminates the rows with - after grouping since your data set seems to have one or more files having a My_Rating but not having a Category property.

Doubling up on the grouping using the list( ... , ...) as group construct gives you the dual grouping you’re asking for, and it additionally stores the result in group so we can showcase it later on. You could possibly want to switch around the order of file.folder and Category, but do remember to then change group[0] to group[1] (and vice versa).

Lastly, in order to sort on the count it can’t be done in the table header definitions, but needs to be done in a FLATTEN statement to store the value readily available for sorting (and filtering and grouping and … ).

Query is untested, but hopefully it works and answers your questions.

(Upon re-reading your request I see you’re talking about column count … folder and sub-folders, I’ve implemented this a separate rows for each folder, was that correct? Or are you aiming to have a dynamic count of columns where each column matches one folder, and displays the count form that folder? (This would be near to impossible to do in an ordinary table (unless you specify folders up front), but doable in dataviewjs (and sorry for all the parentheses ( :smiley: ) ) ) )

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