DataTable column to aggregate if field exists

[Robert_Naccache]

Hello!

First time posting here, I've been looking around but haven't found anything to point me towards the right direction to solve my issue.
I'm creating a Data Table with a Date Histogram as a bucket, and i need to display two columns.

One column is the Total amount of successful Jobs (this is a unique count of the field ['JOBID'] with ['Errorid'] being Null)
second column is the total amount of jobs with errors (this is a unique count of the filed ['JOBID'] with ['Errorid'] having a value other than null)

I've tried using a filter but it applys to the whole table so that doesn't work for me. i also tried using a split rows with two conditions exists:errorid and !(exists:errorid) but this doesn't display it in a seperate column, rather it splits the rows which is not what i want.

my end result should look something like this:

| DateTime | Successful jobs | Failed Jobs |
| Nov.22-2017 | 10 | 3 |
| Nov.21-2017 | 15 | 6 |

Any insight on how to tackle this would be highly appreciated.

Thanks!

[ppisljar (Peter Pisljar)]

i think you have two options ...
preferred if you have a lot of data and need speed: reindex your data:

• add a new field called succesfull which has a value 1 id ErrorId is null, 0 otherwise
• add a new field called failed which has value 1 if ErrorId is not null, 0 otherwise
  then you can simply sum on those fields

second way, should be ok if you don't have tons of data:

• add a scripted field to your index, with painless script like

 if (doc['ErrorId'].value === 'null') return 1;
return 0;

and a similar one for checking if ErrorId is not null for counting errors

• again, do a sum on this scripted fields

let me know if this solves your issue.