Create a table with columns with different tasks

What I’m trying to do

I’m trying to create a table with two columns that take in tags from the tasks I have and display them together on a table.

For example the following three tasks should output to the table below.

  • Task 1 #tag1
  • Task 2 #tag1 #tag2
  • Task 3 #tag2

I want the output to look like the following:

Tag1 Tag2
Task 1 Task 2
Task 2 Task 3

I’ve tried various ways of joining tables from my limited SQL knowledge but either this task is outstripping my knowledge or everything I’m trying isn’t supported in dataview. I’m hoping someone can give me some pointers in the right direction.

Play around with the following in a file of its own:

- [ ] Task 1 #tag1
- [ ] Task 2 #tag1 #tag2 
- [ ] Task 3 #tag2

  regexreplace(tag1tasks.text, "#\S+", "") as Tag1, 
  regexreplace(tag2tasks.text, "#tag2", "") as Tag2
WHERE file = this.file
FLATTEN list(filter(file.tasks, (task) => 
  contains(task.tags, "#tag1"))) as tag1tasks
FLATTEN list(filter(file.tasks, (task) => 
  contains(task.tags, "#tag2"))) as tag2tasks

As it stands it should produce something like:

The trick to this is to use “misuse” FLATTEN list(filter( .... )) as tag1Tasks to build ourselves a new list to present. Note that this, even if applied to multiple files, will produce the tags for each tag from each file in its separate rows.

If you want to collate the tasks across your vault it would require some extra logic, and grouping and what not. The principle though would be similar, that we’d gather all the tasks together, and then filter out those matching the tags you’d like to see.

Then the query would look something like:

TABLE WITHOUT ID tag1tasks.text as Tag1, tag2tasks.text as Tag2
WHERE contains(file.tasks.tags, "#tag1") or contains(file.tasks.tags, "#tag2")
FLATTEN file.tasks as task
WHERE contains(task.tags, "#tag1") OR contains(task.tags, "#tag2")
FLATTEN list(filter(rows.task, (task) => 
  contains(task.tags, "#tag1"))) as tag1tasks
FLATTEN list(filter(rows.task, (task) => 
  contains(task.tags, "#tag2"))) as tag2tasks

This query do a dual filter on the same conditions of #tag1 and #tag2 in order to reduce how many files we actually do the FLATTEN file.tasks as task on. This is done to reduce the overall load of such a query. After that we group all the tasks back together, and filter out the two different categories.