How do you do a conditional Eval?

[marjonhtuazon]

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.

[woodcock]

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

[marjonhtuazon]

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

[woodcock]

This is not a work-around, it is EXACTLY what you asked.

[skoelpin]

Why give up so easy? Start stripping lines off and seeing whats broke