Hi all!
I’m trying to create a table that compares different values of certain fields across notes, but I need the notes to be columns and the fields to be rows.
Think of a “product comparison table” where the first column contains the field names found in all eligible notes, and all other columns are each a note. Each note (column) should show the field values from that note.
Since there is only a small number of notes (columns), and each note has a potentially long list of fields, I need the notes on the x axis and the fields on the y axis, otherwise I end up with a table with 3 rows and 80 columns… that’s the reason.
Example notes
The relevant part of a note looks as follows. It contains inline fields that each have a value.
I picked a movie note type for this example for simplicity.
Note A
#movie
- year:: 2021
- genre:: action
- runtime:: 80 min
- country:: USA
Note B
#movie
- year:: 2021
- genre:: scifi
- runtime:: 1.5h
- country:: Argentina
- score:: 4
Example table output
The dataview table should look like this:
| field | Note A | Note B | Note C |
|---|---|---|---|
| year | 2021 | 2021 | 2023 |
| genre | action | scifi | romance, comedy |
| runtime | 80 min | 1.5h | 120 min |
| country | USA | Argentina | France |
| score | 4 | 7 |
- each note a column
- each field a row
Things I have tried
table without id
year, genre, runtime, country, score
from #movie
This yields the data I’m after, but with inverted columns/rows: the fields are each a column and the notes are rows. I need it the other way around.
Bonus points if you can additionally show me how to dynamically feature all fields from the set of notes without writing each one out in the query.
I tried a number of different queries and scoured the forums and web for a hint, without any luck so far.
Thank you!