# Converting Null values to Zero

**URL:** <https://discuss.elastic.co/t/converting-null-values-to-zero/295707>\
**Category:** Kibana\
**Tags:** canvas\
**Created:** [January 28, 2022, 1:11pm UTC](https://discuss.elastic.co/t/converting-null-values-to-zero/295707 "2022-01-28T13:11:35Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![AmyMCollins](https://avatars.discourse-cdn.com/v4/letter/a/35a633/32.png) [@AmyMCollins](https://discuss.elastic.co/u/AmyMCollins)\
**Post date:** [January 28, 2022, 1:11pm UTC](https://discuss.elastic.co/t/converting-null-values-to-zero/295707/1 "2022-01-28T13:11:36Z")

</div>

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?

```auto
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

---

<div class="post-metadata">

**Author:** ![AmyMCollins](https://avatars.discourse-cdn.com/v4/letter/a/35a633/32.png) [@AmyMCollins](https://discuss.elastic.co/u/AmyMCollins)\
**Post date:** [February 4, 2022, 10:30am UTC](https://discuss.elastic.co/t/converting-null-values-to-zero/295707/2 "2022-02-04T10:30:49Z")

</div>

Solved it by adding two additional map column lines

```auto
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

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [March 4, 2022, 10:30am UTC](https://discuss.elastic.co/t/converting-null-values-to-zero/295707/3 "2022-03-04T10:30:59Z")

</div>

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