Create a new field with categorical values for visualization

[CarbonCriterium]

I would like to create a new field, FlagSC,  based on the value of sc_status.  The new field should have a value of "OK"  when the status is 200, or a value of "Other" for all other statuses.   I intend to use this in a bubble chart with colors based on FlagSC

In theory,  if every cs_uri_stem has at least one event that is status 200 and at least one event that is something else, this could duplicate the number of rows in the output table.

I have tried variations of the code below:

...base search...

| stats values(eval(if(sc_status==200,"OK","Other"))) AS FlagSC, 
 avg(eval(time_taken)) AS avg_tt,  
 avg(eval(sc_bytes)) AS avg_bytes,
 count(eval(source)) AS NumTransactions,  
 BY cs_uri_stem 

| table FlagSC, avg_tt, avg_bytes, NumTransactions 

| rename avg_bytes AS "Average Bytes Returned" avg_tt AS "Average Time in Milliseconds" NumTransactions AS "# of Transactions"

Ultimately, the goal is to have something that might resemble  the following and does NOT include any rows where FlagSC is "OKOther"

cs_uri_stem	FlagSC	avg_tt	avg_bytes	NumTransactions
foo/	OK	...	...	...
foo/	Other	...	...	...
bar/	OK	...	...	...
bar/	Other	...	...	...

[ITWhisperer]

Don't you just need to move FlagSC outside of stats, and add it to your BY clause?

...base search...

| eval FlagSC=if(sc_status==200,"OK","Other") 
| stats avg(eval(time_taken)) AS avg_tt,  
 avg(eval(sc_bytes)) AS avg_bytes,
 count(eval(source)) AS NumTransactions,  
 BY cs_uri_stem, FlagSC 

| table cs_uri_stem, FlagSC, avg_tt, avg_bytes, NumTransactions 

| rename avg_bytes AS "Average Bytes Returned" avg_tt AS "Average Time in Milliseconds" NumTransactions AS "# of Transactions"