Dataview Query to Return All the Unique Values for a Given Field?

[noelle_cd]

Is there a dataview query that can return a list of the unique values for a given field in your vault in alphabetical order?

Basically, I’m making a character tracker using MetaData Menu, and I’d like the options for a select field in my Character Role field to dynamically grow/shrink depending on the values that exist at any given moment.

So if my character roles looked like this:
Character 1 - Role:: Hero
Character 2 - Role: Antagonist
Character 3 - Role - Hero
Character 4 - Supporting

The dataview query would return: Antagonist, Hero, Supporting?

[noelle_cd]

This is how far I’ve gotten:

```dataview
TABLE WITHOUT ID role AS Roles
FROM "Novels/The Last Concubine/Characters"
FLATTEN role
```

Which produces this:

[noelle_cd]

The only part I’m struggling to do is only return the unique values. When I added GROUP BY role it just returned nothing.

[holroy]

I think you’re almost there, but it’s hard to say as we don’t see too much of you base data. I’m guessing that this result might come from the fact that you’re grouping the roles together, and it then makes the single values as into lists, so that what we’re looking at is actually a list of all the unique roles.

A lengthy FLATTEN and GROUP BY example

role:: Contagonist
role:: Antagonist
role:: Hero
role:: Contagonist
role:: Hero
role:: Hero
role:: Contagonist
role:: Hero

## Just a list
```dataview
LIST WITHOUT ID role
WHERE file.name = this.file.name
```
## List with flatten
```dataview
LIST WITHOUT ID R
FLATTEN role as R 
WHERE file.name = this.file.name
```

## Table with flatten and group by
```dataview
TABLE WITHOUT ID R, rows.R
FLATTEN role as R
WHERE file.name = this.file.name
GROUP BY R
```

My somewhat lengthy example, which I suggest you run in your own setting in a note of its own, produces the following output for me:

What we see here is that the two first LIST variations, show first the entire list of roles in a single row, the first bullet point, as a list within that row, the multiple bullet points on the second level.

Next, after the FLATTEN, we see that it now shows each role on a separate row, as all the bullet points are on the first level.

Lastly, in the third example, I’ve used both FLATTEN role as R and GROUP BY R, and in the first column it shows the unique role (which you’re looking for), and in the second column it shows three lists (one row for each unique row) of all the role’s having that particular value, the rows.R(aka all the rows of R which are now grouped together).

If I skipped adding the rows.R to the last example, it would only show the actual unique roles you’re looking for. And maybe, just maybe, your issue is that you didn’t give your flattened value a name (using AS something and just used FLATTEN role which could lead to some ambiguity later on.

---

Summary I think, but I don’t know, since we don’t see your actual query, that the image you produced is showing the equivalent of my rows.R instead of the just the R. So if my explanation above doesn’t help you to understand how your query is wrong, please provide your query and the result of it, and possible with some data leading up to it.

[noelle_cd]

Thanks so much for your details response! It’s definitely helped my understand some of the concepts behind flattening, but I’m beginning to think the functionality I’m looking for may not exist.

The closest I’ve gotten to the result I’m looking for was this query:

```dataview
LIST WITHOUT ID role
FROM "Novels/The Last Concubine/Characters"
```

Which produces:

But I was basically hoping to replicate the Excel function =unique(RANGE) where all that’s returned from the range is one instance of each value (example below - result in column D).

But I’m guessing there’s no way to reproduce this filtered list, so I might be wasting your time.

[holroy]

How does your definition of the fields within markdown look like? You’re not pulling the data from an excel spreadsheet are you?

Or another option, what do you get if you do this query:

```dataview
LIST WITHOUT ID min(rows.role), length(rows.role)
FROM "Novels/The Last Concubine/Characters"
GROUP BY role
```

[noelle_cd]

They way I’m collecting the data is every “Character” page has an inline field called role.

So, for Sarah.md, it would be something like:

# Sarah
Role:: Hero

And all of these files are held within one character folder.

Unfortunately, that query returns an error.

Dataview: Error: 
-- PARSING FAILED --------------------------------------------------

> 1 | LIST WITHOUT ID min(rows.role), length(rows.role) 
    |                               ^
  2 | FROM "Novels/The Last Concubine/Characters"
  3 | GROUP BY role

Expected one of the following: 

'(', '*' or '/' or '%', '+' or '-', '.', '>=' or '<=' or '!=' or '=' or '>' or '<', '[', 'and' or 'or', /FROM/i, EOF, FLATTEN <value> [AS <name>], GROUP BY <value> [AS <name>], LIMIT <value>, SORT field [ASC/DESC], WHERE <expression>

Thanks so much for your help though! May have to just enter the options manually!

[holroy]

HeHe… My bad on that last one, try TABLE WITHOUT ID, not LIST WITHOUT ID

[noelle_cd]

OMG, that worked! Thank you so much!!! I appreciate you taking the time to go back and forth with me on this!