Billboard data analysis

[koentje]

So I have a large datafile which contains the billboard data per week since the 1960's. this is the structure of the data:

As you can see i have filtered on weeks_on_chart showing that 1 album can have multiple entries. I would like to find the highest "weeks on chart" value per variable and output this into a new file which has only 1 entry per album which contains the album entry of the initial file that had the highest weeks on chart. However,  I am struggling with the way on how to do this, any suggestions?

[Maskell_Rascal · Accepted solution]

Hi @koentje 

It sounds like the Summarize tool would be what you need here. You can do a Group By on the Title & Artist fields, and then select the Max Week_On_Chart. 

Sample Data:

Output from Summarize tool:

Cheers!

Phil

[koentje]

does not work properly sadly... when I do that the results do not show the highest on the chart as when I filter descending the highest weeks on chart comes at 500 but as can be seen from my example it is 958

[Maskell_Rascal]

@koentje Not sure why it would be doing that. If can provide the file in question or some sample data, I can take a look at it.