INTERACT FORUM

Please login or register.

Login with username, password and session length
Advanced search  
Pages: [1]   Go Down

Author Topic: help wanted for aggregation functions  (Read 2600 times)

ghappe-MC

  • Recent member
  • *
  • Posts: 31
help wanted for aggregation functions
« on: February 22, 2021, 12:05:07 pm »

Hello experts,

I'm looking for a way to capture, in a variable which i can put in a view, the number of occurrences of a specific value in a field (Tag).

Like displaying the number of albums for a specific artist or the number of occurrences of a specific value in a listfield.

Any suggestions and/or help will be much appreciated.

Greetings, Guido


 
Logged
Having doubts is the privilege of intelligent people, being Ignorant the faith of all the others

zybex

  • MC Beta Team
  • Citizen of the Universe
  • *****
  • Posts: 2674
Re: help wanted for aggregation functions
« Reply #1 on: February 23, 2021, 02:52:18 am »

The closest you can probably get is this:
GroupCountQuery(Artist, Album)

For a given file, this tells you how many different Album names are there for the current file's Artist.
Some caveats though:
- for files with multiple Artists, it only considers the full string name. So a file with "Artist 1; Artist 2" is different from just "Artist 1" or even "Artist 2; Artist 1".
- values are only recalculated once per minute. If you change something, you may need to wait 1 minute to see the updated count
- this is a slow function. Using it in Views might bring them to a crawl
Logged

ghappe-MC

  • Recent member
  • *
  • Posts: 31
Re: help wanted for aggregation functions
« Reply #2 on: February 23, 2021, 03:32:23 am »

Hello Zybex,

Thanks for the reply. I've been playing with this to but it does not solve my problem. What i am trying to do is have a view with just the genre tag and the number of occurrences of every specific value in the genre fields, but that is indeed a multi value field. I might be able to get the wanted result by exporting the data to an external sql server but then i am faced with the problem of getting the data back into MC.
So i'll be likely busy for some time but being in a lock-down time is the thing i have in abundance.

So thanks, and greetings,

Guido   
Logged
Having doubts is the privilege of intelligent people, being Ignorant the faith of all the others

wer

  • Citizen of the Universe
  • *****
  • Posts: 2640
Re: help wanted for aggregation functions
« Reply #3 on: February 23, 2021, 04:24:06 am »

The way you're explaining your question isn't as clear as you think it is... Like this:
What i am trying to do is have a view with just the genre tag and the number of occurrences of every specific value in the genre fields, but that is indeed a multi value field. 
The number of occurrences of the word "Classical" in all genre fields is just the number of files tagged Classical. Things like that are easily displayed in a panes view.

So I can't quite tell what that actually means.  Can you describe in other words, or better, give an example.

As a side note, a SQL server should not be necessary for tabulation activities.  You can copy and paste from the MC files pane into Excel.
Logged

dtc

  • MC Beta Team
  • Citizen of the Universe
  • *****
  • Posts: 3125
Re: help wanted for aggregation functions
« Reply #4 on: February 23, 2021, 09:20:44 am »

If you want to go back to old school MC and want to work with variables, take a look here

https://yabb.jriver.com/interact/index.php/topic,126778.msg878829.html#msg878829

It is based on work from MrC here

https://yabb.jriver.com/interact/index.php/topic,77826.msg528526.html#msg528526

You can use these techniques to aggregate almost anything.

Logged

ghappe-MC

  • Recent member
  • *
  • Posts: 31
Re: help wanted for aggregation functions
« Reply #5 on: February 23, 2021, 10:17:44 am »

Thanks DTC and WER,

Let me describe the problem in an other way. I need a view with just one field and so many records as there are different values of/in the field genre in the library. Lets call this field "Genre Counter". This field should contain, for example "Classical Music # 30271" telling me that there are 30271 records who contain, as one of the values in the genre field, the tag classical music. The next record could look like "Metal # 17840" and the next "House # 2354' etc, etc. Does this brief example clarifies my desire somewhat?

Greetings and many thanks,

Guido
Logged
Having doubts is the privilege of intelligent people, being Ignorant the faith of all the others

ghappe-MC

  • Recent member
  • *
  • Posts: 31
Re: help wanted for aggregation functions
« Reply #6 on: February 23, 2021, 10:34:37 am »

In SQL It would look, simplified,  something like this:

SELECT (Genre), COUNT(Genre) FROM Library GROUP BY Genre ORDER BY (Genre) DESC;

Syntax depending on witch server you would use and in this example not showing the extraction from the individual values in the genre field

Guido
Logged
Having doubts is the privilege of intelligent people, being Ignorant the faith of all the others

zybex

  • MC Beta Team
  • Citizen of the Universe
  • *****
  • Posts: 2674
Re: help wanted for aggregation functions
« Reply #7 on: February 23, 2021, 11:01:15 am »

You can define a View to show the total number of items in each category.
Create a Categories View, click Add... select "Expression". Fill in with:
  Name: Genres
  Expression to group by: [Genre]
  Expression to Display: [Genre] (GroupCount())

Click OK and Save. It should now display the number of items next to each Genre on the tree. You can add other sub-groupings to the View using the same method.

To show the number also on the main panel, set the thumbnail text to "[Name] (GroupCount())"
Logged

zybex

  • MC Beta Team
  • Citizen of the Universe
  • *****
  • Posts: 2674
Re: help wanted for aggregation functions
« Reply #8 on: February 23, 2021, 11:19:21 am »

You can even apply color on the Display Expression:
[Genre, 1] <font color="#00E0E0"> (GroupCount())
Logged

wer

  • Citizen of the Universe
  • *****
  • Posts: 2640
Re: help wanted for aggregation functions
« Reply #9 on: February 23, 2021, 12:55:00 pm »

Thanks DTC and WER,
... "Classical Music # 30271" telling me that there are 30271 records who contain, as one of the values in the genre field, the tag classical music...

Meaning you just want the count of the files that have that tag, which is exactly what I said before.

So I would say Zybex's first answer was correct for what you actually first asked, because you said you wanted a count of albums.  His latest answer is correct if you want files.

Moreover, as I said before, this is even more easily done in a panes view, if you want to do no work at all: click on the category you want, and at the bottom of the window in the status bar, MC shows you the count of files that have that tag. No expression work required.  It's easy to overlook, but MC counts and summarizes everything you select that way.

Finally, if you want a simple list, instead of doing a Category view as Zybex described, do a panes view.  And add a category as described before:

  Name: Genres
  Expression to group by: [Genre]
  Expression to Display: [Genre] (GroupCount())

That will give you a nice list in one place without using the tree.
Logged

ghappe-MC

  • Recent member
  • *
  • Posts: 31
Re: help wanted for aggregation functions
« Reply #10 on: February 23, 2021, 11:56:20 pm »

Hello again,

Fantastic, the crystal clear example provided by zybex is exactly what I need, I just never looked in that direction which shows I have a lot to learn, however, this problem has been solved.

Many thanks zo zybex and wer, ;D

Greetings, Guido
Logged
Having doubts is the privilege of intelligent people, being Ignorant the faith of all the others

JackTheWolf

  • Recent member
  • *
  • Posts: 34
Re: help wanted for aggregation functions
« Reply #11 on: May 23, 2022, 09:03:42 pm »

You can even apply color on the Display Expression:
[Genre, 1] <font color="#00E0E0"> (GroupCount())

It may be simple, but I just wanted to say this helped me immensely with eyeballing Genres.

If I could only find something as useful for the genre / sub-genre debacle (keywords, calc genre(s), etc.)

I wonder if it's possible to somehow retain 'pane tagging' with a calculation like that...
Logged
Pages: [1]   Go Up