Combine 2 data view tables into one

What I’m trying to do

I am trying to combine 2 dataview tables , into a single table , both the tables have the same columns.

this is a table that gets all my tasks that have been completed sorted by date

TABLE L.text as description
FLATTEN file.tasks AS L 
WHERE L.completion
FLATTEN L.completion as date
sort date

this is a table that get all my list items that have a date (these are events that have happened or basic logs of various things)

TABLE L.text as description
FLATTEN file.lists AS L 
WHERE regextest("^\d{4}-\d{2}-\d{2}", L.text) 
FLATTEN date(substring(L.text, 0, 10)) as date
SORT date

(in combination these two tables have a complete list of things that have happened )

both the tables have the same two columns File and description

all i want to do is append the rows of both the tables into the same table and sort the combined by date

Things I have tried

i am trying to combine the tables into a single querry , but this results duplicate rows

TABLE L.text as description
FLATTEN file.tasks AS L 
FLATTEN file.lists AS T

found a way to combine them in dataviewjs

dv.header(2, "Logs")
// get all the pages 
let pg = dv.pages()
const regex = /\d{4}-\d{2}-\d{2}/; // regex for check for date

let data = []

// get all the lists 
for ( const i of pg){
	for (const j of i.file.lists){
		// these are the completed tasks 
		if(j.completed){
			// data.push( [i.file.link, j.text ,new Date(j.completion)])
			data.push( 
				{
					link: i.file.link, 
					text: j.text, 
					date: new Date(j.completion) 
				}
			)
		}
		// these are the list items 
		if(!j.task && regex.test(j.text)){
			// data.push( [i.file.link, j.text, new Date(j.text.match(regex)[0])])
			data.push( 
				{
					link: i.file.link, 
					text: j.text, 
					date: new Date(j.text.match(regex)[0]) 
				}
			)
		}
	}
}
// sort based on date 
data.sort((a,b) => b.date - a.date)

// convert arry objects to arrayt array 
const dataToDisplay = data.map(x => [x.link, x.text]);

dv.table(["file", "description"], dataToDisplay)

the above script is long , is there a better way to do this without using javascript , with the sql query

I think the piece you’re missing is that in the Metadata on tasks and lists there is an implicit field .task showing whether this is a list or task item, which along with a flat(list( ... )) operation could help you get there.

```dataview
TABLE item.task, item.text
FLATTEN flat(list(file.lists, file.tasks)) as item
LIMIT 100
```

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