Converting Null values to Zero

Elastic Stack Kibana
1

Hi,

I am trying to get the cumulative sum of the columns 'Loaded' and 'Worked' in Canvas. I can do it with the below query but when I remove the limit statement it fails. I believe this is because the pivot creates null values ...?

Can you please help me convert the null values to zero?

filters
| essql 
  query="SELECT caseIdentifier, Loaded , Worked FROM (SELECT *  FROM ( SELECT caseIdentifier, archetype, message FROM \"analytics\" where archetype IN ('VALUEA','VALUEB') and message IN ('Y') and \"@timestamp\" > NOW() -INTERVAL 5 DAY) PIVOT(COUNT(message) FOR archetype IN ('VALUEA' AS \"Loaded\", 'VALUEB' AS \"Worked\"))
) Limit 4
"
| mapColumn "sum" exp={math "sum('Loaded','Worked')"}
| math "sum(sum)"
| metric "Sum Test" 
  metricFont={font size=48 family="'Open Sans', Helvetica, Arial, sans-serif" color="#000000" align="center" lHeight=48} 
  labelFont={font size=14 family="'Open Sans', Helvetica, Arial, sans-serif" color="#000000" align="center"} metricFormat="0,0.[000]"
| render

Thanks in advance,
Amy

2

Solved it by adding two additional map column lines

filters
| essql 
  query="SELECT caseIdentifier, Loaded , Worked FROM (SELECT *  FROM ( SELECT caseIdentifier, archetype, message FROM \"analytics\" where archetype IN ('VALUEA','VALUEB') and message IN ('Y') and \"@timestamp\" > NOW() -INTERVAL 5 DAY) PIVOT(COUNT(message) FOR archetype IN ('VALUEA' AS \"Loaded\", 'VALUEB' AS \"Worked\"))
) 
"
| mapColumn "Loaded" exp={if {getCell "Loaded" | eq null} then=0 else={getCell "Loaded"}}
| mapColumn "Worked" exp={if {getCell "Worked" | eq null} then=0 else={getCell "Worked"}}
| mapColumn "sum" exp={math "sum('Loaded','Worked')"}
| math "sum(sum)"
| metric "Sum Test" 
  metricFont={font size=48 family="'Open Sans', Helvetica, Arial, sans-serif" color="#000000" align="center" lHeight=48} 
  labelFont={font size=14 family="'Open Sans', Helvetica, Arial, sans-serif" color="#000000" align="center"} metricFormat="0,0.[000]"
| render
3

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.