Can SPLITROW in Pivot include null fields in the results?

[rrobe07]

I have a simple pivot search that uses SPLITROW to create a table showing the number of events in an index broken down by the field specified after SPLITROW.

| pivot MyModel SomeObject count(SomeObject) SPLITROW SOME.FIELD

What the results do not show is the number of events that do not have the specified field defined (in the JSON event). Is there a way to tell pivot to include those when doing SPLITROW? It doesn’t seem like I can use fillnull since “| pivot” needs to be the first command in the search. I tried using the isNull and then appending the original search:

| pivot MyModel SomeObject count(SomeObject) FILTER SOME.FIELD is Null
| append [search | pivot MyModel SomeObject count(SomeObject) SPLITROW SOME.FIELD ]

but it again doesn’t allow | pivot anywhere but at the very beginning of the search.

[mattgalvin · Accepted solution]

I realize this is an old question but I just figured out a way to accomplish what I needed with this so I thought I'd post what I found. In order to have null fields included, I added an evaluated expression attribute to the data model using this formula:

if(isnull(field),"Field is Null", field)

When I created the pivot from the data model and used the calculated attribute, I could SPLITROW on it and all data is included (since by the time SPLITROW saw the field, there were no null values).

[rrobe07]

Thanks, that works.