Sorry, I’m sure this query already exists, but I can’t find it.
I am currently adding, in my logs files, a holiday:: VA or holiday:: legal holiday, or other data depending on the type of leave taken on the day in question.
So far so good ; I can output, with dataview, a table showing all the leaves taken and the number by type with:
table without id holiday as type, length(rows) as days
from "JOURNAL"
where holiday AND file.day >= date(2023-01-01) AND file.day <= date(2023-12-31)
group by holiday
In the same file, I added the total sum to which I am entitled
VA:: 15
legal holiday:: 7
CPE:: 7
I would like to be able to recover the number of days remaining (to be taken, therefore).
For example,
for “VA”,
I have holiday::VA three times in the “log” folder ;
I’d get 12 (VA - ∑(number of “holiday::VA” in “log”).
Anyone got a clue for a solution?
Without wanting to push too much… the ideal would be to have it in a table
I’m not quite sure if I’m entirely understanding your use case, but I think the are two constructs you need to know about: this and row.
this is used to address fields and values in the current file, as opposed to any of the files in your queries. So doing this.VA in your query would list the value of the field VA from the file where you run the query from.
However, I reckon you don’t want to type it out, so here enters row, which is kind of a pseudovariable allowing you access to the fields through stuff like row["holiday"] or this[holiday]. Note that these return two different things whilst using the same syntax of looking a variable by name. The first will return the actual field (in any of your queried files) called holiday, and the latter will return from current file (due to this), the field named by the contents of holiday.
Some last tips before tackling the query is that the value of the GROUP BY clause is stored in the variable key, and that you can intermediate store calculation using FLATTEN ... as something. Now we can do something like:
```dataview
TABLE WITHOUT ID key as type, days, this[key] - days as remaining
FROM "JOURNAL"
WHERE holiday
AND file.day >= date(2023-01-01) AND file.day <= date(2023-12-31)
GROUP BY holiday
FLATTEN length(rows) as days
```
