The scenario is this.
I have a two field name name joe and bob. if bob help a job it indicate yes as its field value
like this joe="y" and if bob help with a job it will have a value of bob="y" and "n" if they didnt help.
JOE BOB
Y N
Y N
Y N
N Y
N Y
now im trying to make a single field name "assistance" that counts if joe,bob or both of them help in the job
I use:
|eval assistance=if(joe="y" AND bob="y", "both",(if(joe="y", "JOE Only", "BOB Only")))
assistance COUNT
JOE Only - 3
BOB only - 2
both -------- 0
but if I evaluate it, and the result of BOTH is "0", the field "BOTH" never showed when i tabled "assistance". But I NEED the field BOTH to show up so i can show in data visualization that they didn't assist each other.
Like this:
You Existing Search Here
| stats count(eval(Joe=="Y")) AS Joe_Only count(eval(Bob=="Y")) AS Bob_Only count(eval(Joe=="Y" AND Bob=="Y")) AS Both
| transpose column_name=assistance
| rename "row 1" AS COUNT
Here is a run-anywhere example:
|makeresults | eval raw="Y,N Y,N Y,N N,Y N,Y"
| makemv raw
| mvexpand raw
| rename raw AS _raw
| rex "^(?<Joe>.*?),(?<Bob>.*)$"
| stats count(eval(Joe=="Y")) AS Joe_Only count(eval(Bob=="Y")) AS Bob_Only count(eval(Joe=="Y" AND Bob=="Y")) AS Both
| transpose column_name=assistance
| rename "row 1" AS COUNT
thanks for a work around but unfortunately i cant use it to troubleshoot my error on my search query. like if I pipe another search the results always end up on "No search Results" and i dont know why. I need to correlate this output to another data so i can have a good data visualization
but thanks anyway
This is not a work-around, it is EXACTLY what you asked.
Why give up so easy? Start stripping lines off and seeing whats broke