Dataview help with a query calculating an amount from frontmatter emojis

[ribena75]

What I’m trying to do

I am wondering if there is a way in dataview to run a query that populates a table grouping frontmatter values by week/date (file.name) but calculating a numerical amount based on the emojis used in frontmatter… Basically I would like to have a query that calculates my weekly alcohol intake, which I save to frontmatter in Daily Notes as an emoji, i.e. or or or etc (or nothing at all ofc); but I want the query to actually tell me the numerical units of alcohol based on the total emojis used each day. For example:

= 2.5 units (so = 7.5 units)
= 1.5 units (so = 3 units)

Things I have tried

Searched on the forum but couldn’t find what I need (I am a relatively new starter with Obsidian and dataview especially)

[holroy]

In which property do you store your intake? And what do you get if you do:

```dataview
TABLE unit
WHERE drinks
FLATTEN drinks as unit
LIMIT 10
```

Replace drinks with your property name.

Alternatively what do you get if you replace the flatten line with FLATTEN split(drinks) as unit?

Do either of these queries produce a list with singular drinks on each line?

[ribena75]

Hi, see pic.

[ribena75]

Fyi, this is the query I am currently using, but as above, I want to be able to calculate units of alcohol each week (and ideally, summed too!)

TABLE mood, alcohol
FROM "Daily notes"
WHERE file.day >= date(this.file.name) - dur(1 week) AND file.day < date(this.file.name)
SORT file.name asc

shows as

[holroy]

After a little testing, I discovered that doing split( ..., "") on the beer and wine glass indeed was multi-byte unicode characters, which makes the query a little more ugly. But the following seems to work for a simpler test case:

```dataview
TABLE WITHOUT ID drink, singleChar, units, sum(units)
WHERE file = this.file
FLATTEN drinks as drink
FLATTEN regexreplace(regexreplace(drink, 
    "🍺", "B"),
    "🍷", "W")
  as singleChar
FLATTEN list(map(split(singleChar, ""), (c) => object(
 "B", 2.5, "W", 1.5)[c])) as units
SORT sum(units) DESC
```

Here we replace the multi-byte characters with a single byte character, singleChar. These characters are then mapped to a numeric value, and that list is stored into units, and then you could do sum(units) to calculate the total amount.

For my test file this query produces this result:

I leave it up to the reader to adapt this into a weekly consumption query…

[ribena75]

Thank you!! I will have a play around with this.