Canvas if ... or logic

[fpunzo (Frank)]

Hi I am trying to create a RAG (red, amber green) indicator that is based on the results of a query. I am on Kibana Version 6.8.1. I'm trying to add and "OR" condition within an "IF" statement. I am getting the following error after running the query in canvas. Can someone suggest the correct syntax?

Error...

[mapColumn] > [if] > [compare] > Invalid compare operator 'max_record_score'. Use eq, ne, lt, gt, lte, gte

Here is my expression...

filters
| essql 
  query="SELECT sev1hits, sev2hits, 
             max_record_score, total_critical_anomalies
         FROM data3 
         WHERE (sev1hits > 0 OR sev2hits > 0) OR (max_record_score > 80 AND app='XYZ')"
| mapColumn "ragColor" 
  fn={if {getCell "sev1hits" | compare "gte" to=1 OR max_record_score | compare "gt" to=90} then="red" 
      else={
          if {getCell "sev2hits" | compare "gte" to=1 OR max_record_score | compare "gt" to=80} 
          then="yellow" else="green"}}
| if {rowCount | compare "lte" to=0} then={as "ragColor"}
| shape "hexagon" 
  fill={if {getCell "ragColor" | compare "eq" to=null} then="green" 
        else={getCell "ragColor"}} border="#ffffff" maintainAspect=true borderWidth=0
| render

[joshdover]

I believe you're going to need to use the any function instead of OR inside your canvas expression. For example, your mapColumn would like:

| mapColumn "ragColor" 
  fn={if {any {getCell "sev1hits" | compare "gte" to=1} {getCell "max_record_score" | compare "gt" to=90}} then="red" 
      else={
          if {any {getCell "sev2hits" | compare "gte" to=1} {getCell "max_record_score" | compare "gt" to=80}} 
          then="yellow" else="green"}}

Instead of nested if statements, you may also want to check out the switch function instead:

| mapColumn "ragColor" 
  fn={switch
    case={case if={any {getCell "sev1hits" | compare "gte" to=1} {getCell "max_record_score" | compare "gt" to=90}} then="red"}
    case={case if={any {getCell "sev2hits" | compare "gte" to=1} {getCell "max_record_score" | compare "gt" to=80}} then="yellow"}
    default="green"}

Using switch makes it a bit simpler to add additional colors in the future. Let me know if this works!