Get the file name associated with the max value?

What I’m trying to do

How do I get the file name associated with the max value?

lets say, I have multiple baskets (one file each) and fruits associated with the baskets (also one file each):

file: basket_1
type: round

file: cont_1
bask: basket_1 
fruit: apples: 
count: 3

file: cont_2
bask: basket_1
fruit: oranges: 
count: 5

file: cont_3
bask: basket_1
fruit: plums: 
count: 2
--------

file: basket_2
type: square

file: cont_4
bask: basket_2
fruit: pears
count: 4

file: cont_5
bask: basket_2
fruit: apples
count: 6

file: cont_6
bask: basket_2
fruit: oranges
count: 3

I would like to have the highest quantity and the corresponding fruit output for each basket.
Like this:

bask… fruit … max-count
####################
basket_1 … oranges … 5
basket_2 … apples … 6

Things I have tried

my dataview query looks like this:

```dataview
table
max(rows.count) AS C

group by bask
```

leads to this:
grafik

but I only get the number displayed.
Simply writing file.name behind it, would certainly be too easy.
is this even possible with “simple” dataview or does dataviewjs have to do it?

Try the following query, and see if that gives you the wanted result:

## List all candidates, sorted according to basket and count
```dataview
TABLE bask, fruit, count
WHERE bask AND count
SORT bask ASC, count DESC
```

## GROUP them, and pick the first in each set

```dataview
TABLE rows[0].file.link, rows[0].fruit, rows[0].count
WHERE bask AND count
SORT bask ASC, count DESC
GROUP BY bask
```

The trick to doing this, is that we need to make sure that the max value is at an index in the grouped set that we now. Therefore the first step is to produce an intermediate table to get the sorting correct, where the first line in each set is the one value we’re really wanting to get.

Then after finding that query, we can change it into the second variant, we’re we group on the bask, and pick out the first, aka rows[0], member of each the grouped entries. Now you can delete the first query, since it’s no longer needed, but I left it in here to show the process of first sorting it “correctly”, and then grouping and picking out the wanted value.

Thank you holroy,
it works fine :ok_hand:

but how do I have to set the WHERE clause, that only the baskets with type: round will be displayed?

You never mentioned that was a requirement… :upside_down_face:

However, that would require a link between the fruits and the baskets. This normally means you’d need to actually link them together, but I had an idea and tried it out, and it actually worked.

```dataview
TABLE rows[0].file.link, rows[0].fruit, rows[0].count
FLATTEN link(bask) as basketLink
WHERE bask AND basketLink.type = "round" AND count
SORT bask ASC, count DESC
GROUP BY bask
```

This assumes that the value of the bask field is the exact name of the basket note, so that we can use that to create a link back to the basket, and then check it’s type. :smiley:

I wasn’t at all sure this would work, but it did!

1 Like

indeed, it does work!

Wow, :flushed:
chapeau! Thank you very much, also for your patience.

…This assumes that the value of the bask field is the exact name of the basket note…

so to speak a reference field as in 1:n relations?

til next time.
cheers

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.