Dataview - arrays in rows from properties

Help
1

I want to display arrays from properties on their own lines so that tables of data read clearly as rows.

The Dataview queries are to be placed in files where they are only referencing their own YAML headers / properties. Each file stores the data for series I watch: the date watched, the series number, the episode number, and my notes for whatever has been viewed on a specific day.

I have read lots of threads on the forum, read the user docs, and searched online, but I have not been able to get a readable table.

The data and working queries I have tried are below. Using flatten and group by do not change layout that I see from just using a simple query.

With thanks for any help.

---
Date:
  - "[[2024-10-01]]"
  - "[[2024-10-02]]"
  - "[[2024-10-03]]"
  - "[[2024-10-04]]"
  - "[[2024-10-05]]"
Series:
  - "1"
  - "1"
  - "1"
  - "1"
  - "1"
Episode:
  - "1"
  - "2"
  - "3"
  - "4"
  - "5"
Notes:
  - Here are some observations about the episode, the series, the script, the acting, the directing etc
  - Here are some observations about the episode, the series, the script, the acting, the directing etc
  - Here are some observations about the episode, the series, the script, the acting, the directing etc
  - Here are some observations about the episode, the series, the script, the acting, the directing etc
  - Here are some observations about the episode, the series, the script, the acting, the directing etc
---

```dataview
TABLE WITHOUT ID
rows.Date AS Date,
rows.Series AS Series,
rows.Episode AS Episode,
rows.Notes AS Notes
WHERE file = this.file
FLATTEN file.name
GROUP BY file.name
```


```dataview
TABLE WITHOUT ID
Date
, Series
, Episode
, Notes
WHERE file = this.file
```
2

Just to record my failures for my own future reference, this gives a row of dates but the other fields are all blank:

```dataview
TABLE WITHOUT ID
choice(typeof(Date) = "array", Date, list(Date)) AS Date,
choice(typeof(Series) = "array", Series, list(Series)) AS Series,
choice(typeof(Episode) = "array", Episode, list(Episode)) AS Episode,
choice(typeof(Notes) = "array", Notes, list(Notes)) AS Notes
FLATTEN Date
GROUP BY Date
```
3

date works when you flatten it – have you tried also flattening the other fields?

1 Like
4

Thanks. Yes. But alas no jackpot – just rows and columns filled with dashes:

```dataview
TABLE WITHOUT ID
choice(typeof(Date) = "array", Date, list(Date)) AS Date,
choice(typeof(Series) = "array", Series, list(Series)) AS Series,
choice(typeof(Episode) = "array", Episode, list(Episode)) AS Episode,
choice(typeof(Notes) = "array", Notes, list(Notes)) AS Notes
FLATTEN Date
GROUP BY Date
FLATTEN Series
FLATTEN Episode
FLATTEN Notes
```


```dataview
TABLE WITHOUT ID
choice(typeof(Date) = "array", Date, list(Date)) AS Date,
choice(typeof(Series) = "array", Series, list(Series)) AS Series,
choice(typeof(Episode) = "array", Episode, list(Episode)) AS Episode,
choice(typeof(Notes) = "array", Notes, list(Notes)) AS Notes
FLATTEN Date
GROUP BY Date
FLATTEN Series
GROUP BY Series
FLATTEN Episode
GROUP BY Episode
FLATTEN Notes
GROUP BY Notes
```
5

if you structure your data differently, it becomes a lot easier:

---
tags:
 - f93079
data:
- date: "[[2024-10-01]]"
  series: 1
  episode: 1
  notes: some notes
- date: "[[2024-10-02]]"
  series: 1
  episode: 2
  notes: some notes
- date: "[[2024-10-03]]"
  series: 1
  episode: 3
  notes: some notes
- date: "[[2024-10-04]]"
  series: 1
  episode: 4
  notes: some notes
- date: "[[2024-10-05]]"
  series: 1
  episode: 5
  notes: some notes
---

TABLE D.date AS date, D.series AS series, D.episode AS episode, D.notes AS notes
FROM #f93079 
FLATTEN data as D

if you want to keep the current somewhat brittle data structure, you’ll be able to transpose your data in dataview.js

6

As @cheezopath suggest I strongly suggest to reformat your data structure. Either to use the nested (or compound) objects within the property section, or possibly also to use lists where each list item is a different episode. The latter can be interesting to use if you want to include other formatting and so on when describing the episode you’ve seen.

1 Like