Full disclosure, I haven't had a good look to see if there is an answer already because I wouldn't know how to structure the search to find it!
We have a large dataset of website usage metrics and to avoid exponential growth we have developed a star schema with the metrics table and reference tables hanging off of that. The metrics table has a column called group ID, in here there can be many ID's (4-5 digit numbers) separated by comma's, these ID's related to another table called group details which has two columns, the first is group ID, second is the group name. What I need to do is a count of events for each group name, I split the columns by a delimiter and tried a few things but didn't even get close.
Hope that made sense! Does anyone have any ideas I can try?
I've created a sample of data here, in the XLSX there is two tabs, a reference table tab, and a data table tab. The data table has a column called groups which indicates which groups the item belongs in. The reference table has group name and group_id as the names. The IDs in the groups (separated by commas) links to the group_id so you can reference the name.
Basically, as output I want to be able to see which group name in each item in the data table belongs to, easy if there is only one ID but I'm not skilled enough to work it out for multiple IDs.