counting using group by multivalue fields that might contain empty or inconsistent fields?

[moaf13]

So i have scenario where i have to group by a table (Make, model, horsepower year) like the one below,

Make              model(mvFields)       horspower(mvFields)               year(mvFields)       comment
Toyota             camry                     175                           2013             (empty field)
                  corolla                     120                          2013             (empty field) 
                   camry                 (empty field)                     2013             (empty field)
separator
Honda             accord                     180                           2013              (empty field)
                  civic                       115                      (empty field)         broken tail light
                  accord                      180                         2013               (empty field)

Now i have used eval comb=mvzip(model,horsepower,",")| eval comb=mvzip(comb,comment) so whenever I try to combine empty fields, the field comb returns null or empty. My goal here is to have a count of unique group by of all the fields combined

for example

Toyota        camry              175                 2013                   (empty field)              count=1 
Toyota        camry          (empty field)           2013                   (empty field)              count=1
Honda        accord             180                  2013                   (empty field)              count=2

if there are other ways of doing this, please share. thanks

[sundareshr · Accepted solution]

Is there a reason stats count by Year Make Mode, HP will not work?

[moaf13]

It gives out random results since it is multi-value field.

[sundareshr]

So you raw data has mv fields? What format is the raw data in? Can you use SEDCMD to replace blank values with "null value"?

[moaf13]

Yes that is helpful, Thank you!