Ad-hoc tracking in Dataview of games won

[anon63144152]

I am playing a game each day with a friend.

I would like to keep an ad-hoc tracker in my journal of the number of games played each day and who won. Thought I could use simple inline fields, such as playing three times on one day and noting the wins as:

---

• P1:: 2
• P2:: 1

---

In the query below, the WHERE will be changed to FROM to target my journal folder.

```dataview
TABLE
length(rows.P1) as P1Wins,
length(rows.P2) as P2Wins
WHERE file.name = this.file.name
```

The query always returns 0 for both players.

I have also tried sum(rows.P1) AS P1Wins and adding FLATTEN for P1 and P2 but without seeing any changes. Adding GROUP BY also doesn’t help.

I was hoping for:

File	P1Wins	P2Wins
2023-06-06	2	1
2023-06-07	2	2
TOTAL	4	3

Perhaps the inline fields should be written in a different format.

It feels as though this should be straightforward, but I seem to be getting further and further from a solution with every attempt I make.

Any tips gratefully received.

Thanks

[anon63144152]

Well, this works for data collection, but not for totalling each player’s wins:

***
- P1:: 2
- P2:: 1
- P1:: 3
- P2:: 4
***

```dataview
TABLE
P1,
P2
WHERE P1 OR P2
```

[anon63144152]

Pretty sure there must be better ways to do this, but the following works to sum inline fields where there is one key and one value in multiple source notes. Can’t find a way to put all of this into one query but – heigh-ho – I am a happy . Six hours well spent.

***
- P1:: 5
- P2:: 1
***

## Total games
```dataview
TABLE WITHOUT ID
sum(rows.P1) as P1,
sum(rows.P2) as P2
WHERE P1 OR P2
GROUP BY P1
```

## Games by day
```dataview
TABLE WITHOUT ID
file.link AS Date,
P1,
P2
WHERE P1 OR P2
SORT file.name DESC
```

[holroy]

Hi there @anon63144152, here are some queries for you to ponder over, and maybe spend even more hours on studying!

## Base query
```dataview
TABLE P1, P2
WHERE file.folder = this.file.folder
  AND file != this.file
```

## Grouped by file
```dataview
TABLE
  rows.P1, flat(rows.P1), sum(flat(rows.P1)),
  rows.P2, flat(rows.P2), sum(flat(rows.P2))
WHERE file.folder = this.file.folder
WHERE P1 OR P2
GROUP BY file.name as GamesPlayed
```

## Every game with game count(?)
```dataview
TABLE WITHOUT ID P1Wins, P1Count, P2Wins, P2Count,
 "<progress value='" + round(100 * P1Wins / (P1Wins + P2Wins), 0) + "' max='100'>" as "Percentage P1",
 "<progress value='" + round(100 * P2Wins / (P1Wins + P2Wins), 0) + "' max='100'>" as "Percentage P2"
 
WHERE file.folder = this.file.folder
WHERE P1 OR P2
GROUP BY true
FLATTEN sum(flat(rows.P1)) as P1Wins
FLATTEN sum(flat(rows.P2)) as P2Wins
FLATTEN length((rows.P1)) as P1Count
FLATTEN length((rows.P2)) as P2Count
```



## With totals
```dataviewjs

function highlight(value) {
  return "**" + value + "**"
}

const result = await dv.query(`
  TABLE
    sum(flat(rows.P1)) as "Player 1",
    sum(flat(rows.P2)) as "Player 2"
  WHERE file.folder = this.file.folder
  WHERE P1 OR P2
  GROUP BY file.name as GamesPlayed
`)

if (result.successful) {
  let p1sum = 0
  let p2sum = 0
    
  dv.array(result.value.values)
    .forEach(v => {
      p1sum += v[1]
      p2sum += v[2]
    })

  result.value.values.push([highlight("Total"), highlight(p1sum), highlight(p2sum)])
  
  dv.table(result.value.headers, result.value.values)
} else
  dv.paragraph(`~~~~\n${ result.error }\n~~~~\n`)
```

Some comments on the various queries, where the base query is the first:

Here we can see that my test setup has four files, one with no mention of P1/P2, One game with one mention, so it’s a single value, and Two game and Three game with two and three values of the P1&P2 combo. I’m not quite what these mention correspond to, I reckon maybe different game sessions during that particular day or something like that?

The next query shows some data when grouped by file (or day in your case):

The interesting thing to note here is that rows.P1 (and the same applies for P2, but I’m only going to talk about P1 from here on), is a multi-level list. I therefore use a new function of dataview (from v0.5.56) to flatten the list to one level, so that flat(rows.P1) is a conform one-level list. This allows for it to be summed nicely. (Doing sums(rows.P1) isn’t able to sum down through the levels)

Then we continue to sum up all games in a slightly different matter, but the principle of using flat() albeit I now use GROUP BY true to sum every game. This results in:

Here I also added the progress bar just for the fun of it, to show the percentage of game wins for the player. Still not sure what the P1Count denotes, whether that is number of play sessions during that day, or what not. It’s there, and hopefully you can make sense out of that value (or just totally ignore it).

The last query does the “impossible” of adding a row to the query to get the totals in a separate row. This displays as:

Either of these last queries could be grouped by either file.name, file.link or true and get similar results, just with different groupings. The trick to this last query is that I’ve beforehand have made sure that the query within the dataviewjs query is producing the wanted resulted (except for the total), and then I use a second loop on this result set to build up the values used for the total row, which is then added at the end before presented to the end user.

Please do ask, if there is something unclear in either query. If needed, one could also add some extra styling to the last line, either through the highlight() function, or also through some extra magic using CSS.