How do I count unique values in a multi value field?

[aarontmartin165]

I have a field cat which may display multiple fields of varying count FFIEC, GLBA, PPI or just PPI so there is no set count to the multivalue fields. I am attempting to count the number of times each unique value appears and graph it over time. My query is as follows:

my query | eval Policies=split(cat,";") | timechart span=1h count by Policies

My problem is that when the line chart is displayed there can be multiple lines for a Policy value. For example if the multivalue field returns 3 instances as follows

FFIEC; GLBA; PPI
PPI
PPI
FFIEC; GLBA; PPI

My line values would display PPI = 2 FFIEC = 2 GLBA= 2 PPI = 2

What I am hoping to achieve is PPI=4 FFIEC=2 GLBA=2

Can anyone identify the part of my query I have wrong?

[evsmt]

Use mvexpand to create an event for each multi value value. You'll be able create a timechart with a line for each distict policy:

my query 
| eval Policies=split(cat,";") 
| mvexpand Policies
| timechart span=1h count by Policies

[p_gurav]

Can you try:

my query | eval To_count=mvcount(split(cat,"; "))-1  | timechart span=1h values(To_count) by Policies

[aarontmartin165]

Why would I use the -1?

[damien_chillet · Accepted solution]

You should try adding a space in your split separator:

my query | eval Policies=split(cat,"; ") | timechart span=1h count by Policies

[aarontmartin165]

That appears to have worked but why?
If
cat= FFIEC; PPI
and
cat= PPI

There is no ; or " " on the second value so why would the "; " be any different than ";"?

[damien_chillet]

split function will create a value for the multivalve field overtime it meets the splitter.
So, in first case "cat=FFIEC; PPI" it will return "FFIEC" and " PPI" if you use ";"
In second case it will just return "PPI" because nothing to split.

[aarontmartin165]

Got it, I was thinking that leading spaces would be dropped but as I read your explanation I realize I had no reason I should have expected that.

[Sukisen1981]

what happens if you just mvexpand on policies before the timechart?