Assume a root folder with several subfolders. I’m trying to retrieve the files that exist in the subfolders and display them grouped by their main subfolder name. So far I have the following code:
```dataview
table
split(replace(file.folder, "Evaluations/", ""), "/")[0] as "Project",
file.mday as "Modified"
from #evaluation
where length(split(replace(file.folder, "Evaluations/", ""), "/")) > 1
This code prints one line for each file found and if I try to group the results by:
table
split(replace(file.folder, "Evaluations/", ""), "/")[0] as "Project",
file.mday as "Modified"
from #evaluation
where length(split(replace(file.folder, "Evaluations/", ""), "/")) > 2
group by Project
the result is an empty table. How can I achieve that the “Project” value prints only once for all files in that project?
In principle yes. I changes your “/” to the parent folder that I’m interested in and changed also the list to table. So, now I get a column with the folder names and a column with the files. I’m wondering if there is a way to group by folder name, so that these values appear only once per group (like doing a merge in excel).
What I have achieved is a table with all files and their respective folder names on the right hand-side (column Project). I want the name of the project to appear only once for each group and if possible centered in the middle of the respective rows (like the cell merge function in excel). Not sure though if this is possible.
Be aware that when doing that you will get potentially unrelated lists for each Project row. So the solution @Craig suggests is the way to do it, but if one of your files have a missing entry for any columns the related lists could be skewed.
Maybe this is better understood with an example. Say a given project has five files, and you want to list the “Status” of each file within that project. Sadly you missed adding the status to one of the files. Then you’ll end up with a table row for that project displaying the project name in one column, the five files in another column and the four status values in the last column (potentially with a - as the fifth element in some cases).
This might not be a problem for you, and in your example you’re using the modified date which will be present for all files, so you might not see this issue.
Another variant of this issue is that if you have columns where the text related to each file is of different length. Then the lines of any columns within a project row won’t necessary align with each other.
Notice how the lines for the “Lost” status doesn’t match up for the Amount and rows.text column.
Is there a solution to this? Not when using GROUP BY, but if one use a query called from dataviewjs, you can use some magic and keep the results ungrouped and then hide repeated values in one or more columns using CSS.
This however is indeed a very hack of a solution, and not nearly as neat as just using GROUP BY to produce the result. In the thread below I showcase one possible approach to this.
