sum comma separated numbers seen as strings

[WyattNW]

My report is meant to show by employee and by category total expenses.  Im trying to sum values that are separated by commas with respect to each category, A category for example is "Dinner Max 18.90". 

My main issue is i cant convert the Comma separated numbers into single rows that respect the category there a part of. any help would be appreciated. 

 

[ben_davis-16359]

You will probably need someone to model this in a tabular format by parsing the category and value lists into rows. 

If this is in BigQuery you may be able to do this in Looker by creating a view containing SQL using the Unnest( )and Split() functions to parse the values on the fly.  I have only ever done this once to split a list of strings but not for aggregating a sum.  Here is a link for reference in Unnest() and Split().

Question:  just so I understand, are the categories in row 6 from your example "Diner - max $14.30, Diner - max $18,90"  is this one long category label, OR is it 2 Categories "Diner - max $14.30" and "Diner - max $18,90" OR 3 Categories "Diner - max $14.30" and "Diner - max $18" and "90"?  Essentially is this a single category or a list a of Categories separated by commas?  If its a single long category then all that needs to be parsed into rows are the values.