Dataview table query using custom column header to create another column

What I’m trying to do

I have a Dataview table with three columns, where the third is an extension of the previous columns. Since the calculations to create the previous columns are rather long, I would like to avoid repeating it all for the third column. Is it possible to refer to a previous column in a latter column?

Things I have tried

I have tried to use the custom column header of the previous columns for the calculation in the third column…but this isn’t working. See example below where the first column has the custom header “done” and the second column has the custom header “challenge”, and I refer to “done” and “challenge” in the third column with the custom header “celebrate”.

	choice(tasks.completed OR tasks.status = "-", "true", "false") as done,
	choice(tasks.text = "challenge", "true", "false") as challenge
	choice(done = "true" AND challenge = "true", "🥳", "😢") as celebrate,
FLATTEN file.tasks as tasks

The result should look like this:

done challenge celebrate
false true :cry:
false false :cry:
true false :cry:
true true :partying_face:

Thank you for your help!

No, but don’t be discouraged as there is a very nice workaround. Change your query to something like the following:

TABLE WITHOUT ID done, challenge, celebrate

FLATTEN file.tasks as task
FLATTEN choice(task.completed OR task.status = -, true, false) as done
FLATTEN choice(task.text = challenge, true, false) as challenge
FLATTEN choice(done = true AND challenge = true, 🥳, 😢) as celebrate

And this could possibly be simplified to:

TABLE WITHOUT ID done, challenge, celebrate

FLATTEN file.tasks as task
FLATTEN (task.completed OR task.status = "-") as done
FLATTEN (task.text = "challenge") as challenge
FLATTEN choice(done AND challenge, "🥳", "😢") as celebrate

The parentheses are possibly not needed, but I do believe they aid in the readability of the statements.

Hopefully these untested queries work, and solves your issues.

(On a sidenote, please do FLATTEN file.tasks as task so that you don’t (possibly) hide the original tasks lists, and make it clearer that from now on you’re working on a single task :smiley: )

Update: Updated example on tasks to be specific to file.tasks

Thank you for the quick reply! Unfortunately I’m having trouble getting your two suggestions to work. I don’t get an error but just a dash for “celebrate”, i.e., emojis aren’t coming up. I’m wondering whether it’s not connecting the “done” and “challenge” to the previous data commands. Any ideas why or what else to try?

Regarding the sidenote, good tip to use singular “task” rather than “tasks”. I changed it to FLATTEN file.tasks as task but FLATTEN tasks as task, as in your text, doesn’t work work for me. I believe this is because I use it with a WHERE command.

I see that I was a bit sloppy on the quotes in my first query in the previous post, so I’ve updated it, but now I’ve also tested my query and changed it slightly related to the challenge bit, as that was only doing a full match on the text “challenge”. Using icontains() we can do a case insensitive search instead, which might be a better option?

If you’ve got a problem with your WHERE clause, please feel free to post it so we can help you troubleshoot it. If you put the following in a note of itself, it returns the expected(?) output:

## Local tasks

- [x] This is done
- [-] This is cancelled 
- [ ] challenge
- [x] challenge
- [ ] What about this chaLLenge?

## Queried tasks

TABLE WITHOUT ID task.text, done, challenge, celebrate
WHERE file = this.file
FLATTEN file.tasks as task
FLATTEN (task.completed OR task.status = "-") as done
FLATTEN icontains(task.text, "challenge") as challenge
FLATTEN choice(done AND challenge, "🥳", "😢") as celebrate

Gives this output:

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