Use Variables in Dataview

[ZoltObsi]

What I’m trying to do

I use Full Calendar Plugin for timetracking an a dataview query for listing tasks an the time spent on them in my daily note.

the datview query looks like this:

TABLE without id
    dateformat(date,"yyyy-MM-dd") as "Start date",
    dateformat((endDate), "yyyy-MM-dd") as "End Date",
    title AS "Task",
    folder AS "Area",
    startTime AS "started",
    endTime AS "finished",
       dur( date(dateformat(endDate, "yyyy-MM-dd") +"T" + endTime) - date(dateformat(date,"yyyy-MM-dd")+"T"+ startTime)).hours
 as "Duration"
 From "00_Timeline/Job"
WHERE contains(dateformat(date, "yy-MM-dd"), "24-06-15") 
sort date ASC, startTime

This only works for tasks spanning multiple days because Full Calendar only then adds endDate to the frontmatter.

I’d like it to work with those finished in the same day an those spanning multiple days.

Things I have tried

The first thing that came to my mind was:
Check if “endDate” exists and if not set “date” for “endDate”

but as threre doesn’t seem to be in if/the/else in dv I’m stuck.

Thanks in advance Zoltan

[cheezopath]

check out choice for an if-else like function that works in regular dataview
https://blacksmithgu.github.io/obsidian-dataview/reference/functions/#choicebool-left-right

[ZoltObsi]

Thanks cheezopath! That works!
is there a way to assign the value to a variable an d use it later?
I hoped for this:

[!tldr+] Timetracking

	TABLE without id
	    end_date= choice(!endDate,date, endDate), 		
		dateformat(date,"yyyy-MM-dd") as "Start date",
		dateformat((endDate), "yyyy-MM-dd") 	as "End Date",
		title AS "Task",
		folder AS "Area",
		startTime AS "started",
		endTime AS "finished",
		dur( date(dateformat(endDate, "yyyy-MM-dd") +"T" + endTime) - date(dateformat(date,"yyyy-MM-dd")+"T"+ startTime)).hours
		 as "Duration"
	From "00_Timeline/Job"
	WHERE contains(dateformat(date, "yy-MM-dd"), "24-06-15") 
	sort date ASC, startTime

but it didn’t work

[holroy]

Definitely after the FROM, and most likely after the WHERE line you could add:

FLATTEN choice(!endDate, date, endDate) as myEndDate

This would assign your value to myEndDate which could be used elsewhere in your query(both later on, and in the column definitions.

[ZoltObsi]

Wow, thank you very much.
That’s great althoug I don’t quite get it, it works now.

This:

[!tldr+] Timetracking

	TABLE without id
		dateformat(date,"yyyy-MM-dd") as "Start Date",
		dateformat(myEndDate, "yyyy-MM-dd") 	as "End Date",
		title AS "Task",
		folder AS "Area",
		startTime AS "started",
		endTime AS "finished",
		dur( date(dateformat(myEndDate, "yyyy-MM-dd") +"T" + endTime) - date(dateformat(date,"yyyy-MM-dd")+"T"+ startTime)).hours
		 as "Duration"
	From "00_Timeline/Personal"
	WHERE contains(dateformat(date, "yy-MM-dd"), "24-06-15") 
	FLATTEN choice(!endDate, date, endDate) as myEndDate
	sort date ASC, startTime

outputs that without endDate:

and this is frontmatter contains endDate: