Dataview, Nested YAML, Help with FLATTEN

[dhniceday]

Hello Obsidian friends,

In my daily notes I have entries for my sport activities.
Example:

sport:
  - id: 1
    type: 'Gehen'
    distanz: 1.4
    dauer: 18
    geschwindigkeit: 4.6 
    hf-schnitt: 117
    puls-max: 107
    memo: ''  
  - id: 2
    type: 'Ellipsentrainer'
    distanz: 
    dauer: 5
    geschwindigkeit: 
    hf-schnitt: 145 
    puls-max: 160
    memo: 'Physiotherapie'

I would like to query my statistics only for the activty “Gehen”.

Things I have tried

A lot of trial and error. My closest result is this:

```dataview
TABLE  
	sport.type
FROM "10 - Daily Notes"
WHERE sport
FLATTEN sport.type as T
GROUP BY T
```

I am not able to only show “Gehen” and to calculate sums, averages or max values in this data structure.

I have quite some experience in dataview, but have never used this datastructure in YAML.

Can you help me with the dataview, or propose a more suitable datastructure to log my activities ?

If it helps, I am able to put out a table of all of my activities with the related data like so:

TABLE WITHOUT ID
	link(file.name) as "Datum",
	sport.type as "Aktivität", 
	sport.distanz as "km", 
	sport.dauer as "Minuten", 
	sport.geschwindigkeit as "km/h", 
	sport.puls-max as "max. Puls",
	sport.hf-schnitt as "∅ HF", 
	sport.memo as "Notiz"
FROM "10 - Daily Notes"
FLATTEN sport
WHERE sport 

Cheers,
Daniel

[mnvwvnm]

Let’s say you use this yaml structure:

---
sport:
  Gehen:
    distanz: 1.4
    dauer: 18
    geschwindigkeit: 4.6 
    hf-schnitt: 117
    puls-max: 107
    memo: ''  
  Ellipsentrainer:
    distanz: 
    dauer: 5
    geschwindigkeit: 
    hf-schnitt: 145 
    puls-max: 160
    memo: 'Physiotherapie'
---

Then you can use this type of queries:

Gehen

(a table for all “Gehen” exercises)

TABLE  
	G.distanz AS "km",
	G.dauer AS "Minuten",
	G.geschwindigkeit AS "km/h",
	G.puls-max as "max. Puls",
	G.hf-schnitt as "∅ HF"
FROM "10 - Daily Notes"
WHERE sport
FLATTEN sport.Gehen AS G

(a table for calculations)

TABLE WITHOUT ID
	sum(rows.G.distanz) AS "total km",
	round(sum(rows.G.distanz)/length(rows), 1) AS "average km",
	sum(rows.G.dauer) AS "Minuten",
	max(rows.G.dauer) AS "longest",
	round(sum(rows.G.geschwindigkeit)/length(rows), 1) AS "media km/h",
	max(rows.G.puls-max) AS "max. Puls",
	round(sum(rows.G.hf-schnitt)/length(rows)) AS "average ∅ HF"
FROM "10 - Daily Notes"
WHERE sport
FLATTEN sport.Gehen AS G
GROUP BY true

I think a new beta version has the new function average… but I haven’t tried it yet. As you can see I do that with sum/length.

[dhniceday]

Just perfect. Thank you.

I have added a list (array) for the type.

sport:
  type: ['Gehen','Ellipsentrainer','Kreistraining']
  Gehen:
    distanz: 1.4
    dauer: 18
    geschwindigkeit: 4.6 
    hf-schnitt: 117
    puls-max: 107
    memo: ''  
  Ellipsentrainer:
    dauer: 5
    hf-schnitt: 145 
    puls-max: 160
    memo: 'Physiotherapie'  
  Kreistraining:
    dauer: 60
    memo: 'Physiotherapie'

to seperate my queries into paragraphs with

WHEN contains(sport.type, "Gehen")

Thank you very much !