Hi all - can anyone help me with the below?
I have a dataset with 200+ fields (some numeric fields, some text fields) and there are multiple rows for some companies included in the dataset.
I need to summarise the data for each unique combination of Company and reporting date. In particular, I need to, for each unique combination of Company and reporting date:
- calculate the weighted average (weighted by a monetary amount) for every numeric field; and
- select the text value for the record with the highest monetary amount for every text field.
What would be the most efficient way to do this? I'm expecting there to be a few stages rather than one magic solution - but am struggling to come up with an easy way to do this.
I've attached a brief extract of what the data looks like to help visually (this is dummy data and not the actual data as I cannot share that), as well as what the desired output would look like.
Any help would be appreciated!
