Extracting a row of data from Dataview

[eightning]

The note below is for a film that has been viewed three times in three different locations by the same three people.

The # Full Listing Dataview table creates a summary of the metadata, detailing when, where, and how the film was watched and the scores out of ten that each person gave for each viewing. If they didn’t attend a viewing, the score is listed as "NA".

In the second # Tokyo Only Dataview table, I would like to extract just the data relating to Tokyo: date, how, and scores.

Using FLATTEN WhereWatched, I can see Tokyo as the only city listed, but I still have the data for all the dates and viewings.

I have tried lots of different ideas, all without success.

Is it possible to extract just the data (entire row) relevant to the viewing in Tokyo?

---

# Title:: More Banshees of Inisherin (2023)

[![More Banshees of Inisherin (2023)|200](https://m.media-amazon.com/images/M/MV5BM2NlZDI0ZDktNTg5OS00ZjQ1LWI4MDEtN2I0MDE5NWRiNzA4XkEyXkFqcGdeQXVyMTY5Nzc4MDY@._V1_SX300.jpg)](https://www.imdb.com/title/tt11813216)
***
> [!note]- ### More Banshees of Inisherin (2023) | [IMDb](https://www.imdb.com/title/tt11813216)   
> **Aka**:: Where Have All the Fingers Gone?   
> **Year**:: [[2022]]  
> **Runtime**:: 114 min  
> **Directors**:: [[Martin McDonagh]]  
> **Writers**:: [[Martin McDonagh]]    
> **Actors**:: [[Colin Farrell]], [[Brendan Gleeson]], [[Kerry Condon]]  
> **Country**:: [[Ireland]], [[United Kingdom]], [[United States]]  
> **City**:: [[Banshee City]]  
> **IMDb**:: [IMDb](https://www.imdb.com/title/tt11813216)   
> **IMDbRating**:: 8.0   
> **Poster**:: https://m.media-amazon.com/images/M/MV5BM2NlZDI0ZDktNTg5OS00ZjQ1LWI4MDEtN2I0MDE5NWRiNzA4XkEyXkFqcGdeQXVyMTY5Nzc4MDY@._V1_SX300.jpg   
> **Genre**:: [[Comedy]], [[Drama]]   
> **WhenWatched**:: [[2023-01-13-Friday]], [[2022-12-26-Monday]], [[2021-12-31-Friday]]    
> **WhereWatched**:: [[New York]], [[London]], [[Tokyo]]   
> **HowWatched**:: [[Cinema]], [[Apple TV]], [[Private screening]]   
>  **PersonA**:: 7, 8, 7   
> **PersonB**:: 10, "NA", 9    
> **PersonC**:: "NA", "NA", 6   
> **Tags**:: #fictionalfilms

# Full Listing
```dataview
TABLE WITHOUT ID
	choice(typeof(WhenWatched) = "array", WhenWatched, list(WhenWatched)) AS When,
	choice(typeof(WhereWatched) = "array", WhereWatched, list(WhereWatched)) AS Where,
	choice(typeof(HowWatched) = "array", HowWatched, list(HowWatched)) AS How,
	choice(typeof(PersonA) = "array", PersonA, list(PersonA)) AS PersonA,
	choice(typeof(PersonB) = "array", PersonB, list(PersonB)) AS PersonB,
	choice(typeof(PersonC) = "array", PersonC, list(PersonC)) AS PersonC
FROM
	#fictionalfilms
SORT 
	max(WhenWatched.file.day) 
	DESC
```

# Tokyo Only
```dataview
TABLE WITHOUT ID
	choice(typeof(WhenWatched) = "array", WhenWatched, list(WhenWatched)) AS When,
	choice(typeof(WhereWatched) = "array", WhereWatched, list(WhereWatched)) AS Where,
	choice(typeof(HowWatched) = "array", HowWatched, list(HowWatched)) AS How,
	choice(typeof(PersonA) = "array", PersonA, list(EPM)) AS PersonA,
	choice(typeof(PersonB) = "array", PersonB, list(KLM)) AS PersonB,
	choice(typeof(PersonC) = "array", PersonC, list(BCM)) AS PersonC
FROM
	#fictionalfilms
FLATTEN
	WhereWatched
WHERE
	contains(WhereWatched, [[Tokyo]])
SORT 
	max(WhenWatched.file.day) 
	DESC
```

Thanks in advance for any pointers or solutions.

[holroy]

@eightning

Is it possible to extract just the data (entire row) relevant to the viewing in Tokyo?

Is this a one time deal, or will you be needing to do similar stuff in the future?

And, how did you produce the data in the first time, because collating the various status like that, is not beneficial for later processing and it would be better to have different structure in the start.

[eightning]

Hello.

Thanks for the reply.

Yes, I hope to do something similar with the same data source, running queries for places, people, dates, etc, if I can.

This is a progression from this thread:

Two dates in one metadata key?

I store films in Obsidian: one note for each film. I query the films using Dataview, with a default sort based on a DateWatched key: **DateWatched**:: [[2022-12-09-Friday]] It works perfectly. However, if I watch the film again and add a second date: **DateWatched**:: [[2022-12-26-Monday]], [[2022-12-09-Friday]] … the film disappears from the Dataview query completely. I assume this is because Dataview can only query one date in a date field and that I just need to restructure the metada…

I have about 1,000 notes all in the same format. Too much work to rewrite them all, so I will do as much as I can and learn from the experience.

It would be nice to draw out the data as in the first post, but if I have to leave things grouped together that will be fine. Just a personal collection of data: nothing critical or of interest to anyone else.

[holroy]

The gist of the idea is as follows:

• Do the full list query within dataviewjs, and store the list in fullList
• Then just filter out the information you want, aka fullList.filter(... ) into
• Present the data again, using dv.table()

Here is something to explain how to do this:

```dataview
TABLE WITHOUT ID mood, moody
FROM "ForumStuff/f52118"
WHERE mood
```

A random table, which produces this output in my test vault:

And now the real script, where I want to filter out any moody larger than 10, you’ll get to adapt to your situation.

```dataviewjs
// Get the full list
const pages = await dv.query(`
TABLE WITHOUT ID mood, moody
FROM "ForumStuff/f52118"
WHERE mood
`)

if (pages.successful) {
  // Filter out the result you want, remember that the first column is 0
  const filtered = pages.value.values
    .filter(r => r[1] > 10)
  
  // Present the result
  dv.table(pages.value.headers, filtered)
}
else
  dv.paragraph("~~~~\n" + pages.error + "\n~~~~")
```

Which aptly produces:

Hope this helps, now I’m going out for pizza!

[eightning]

Very grateful for the help. Brilliant. I’ll try to get my head around everything and see what I can do.

Enjoy the pizza.

T H A N K S