# Query using ESSQL Conditional (CASE) Function missing some records

**URL:** <https://discuss.elastic.co/t/query-using-essql-conditional-case-function-missing-some-records/278552>\
**Category:** Kibana\
**Tags:** canvas\
**Created:** [July 13, 2021, 1:55pm UTC](https://discuss.elastic.co/t/query-using-essql-conditional-case-function-missing-some-records/278552 "2021-07-13T13:55:15Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![girvandip](https://avatars.discourse-cdn.com/v4/letter/g/a87d85/32.png) [@girvandip](https://discuss.elastic.co/u/girvandip)\
**Post date:** [July 13, 2021, 1:55pm UTC](https://discuss.elastic.co/t/query-using-essql-conditional-case-function-missing-some-records/278552/1 "2021-07-13T13:55:15Z")

</div>

Hello,

I am working with Canvas in Kibana. Below is the example of the data in tabular format:

```auto
| timestamp | Id | message |
---------------------------------
| 08:49:10 | 1 | TE |
| 08:50:20 | 1 | BE |
| 09:00:10 | 1 | Success |
| 09:00:50 | 2 | TE |
| 09:01:20 | 2 | BE |
| 09:20:00 | 3 | BE |
| 09:20:10 | 3 | Success |
| 09:20:30 | 4 | TE |

```

I am trying to display a metric in Canvas where it counts the number of Ids where their last message (the message with the highest timestamp) is "Success". In the given dataset above, the metric would display "2".

I have used the following expression and it works fine until it reached a certain number of records:

```auto
filters
| essql
 query={​​​​​​​​string"SELECT(CASE WHEN first(message)='Success' THEN 1 ELSE 0 END)
 as cnt FROM \"" {​​​​​​​​var"IndexName"}​​​​​​​​ "\" WHERE Id IS NOT NULL
group by Id"}​​​​​​​​
| math"sum(cnt)"
| metric
 metricFont={​​​​​​​​font family="Arial, sans-serif" size=36 align="center" color="#93969D" weight="normal" underline=false italic=false}​​​​​​​​ 
 labelFont={​​​​​​​​font size=14 family="'Open Sans', Helvetica, Arial, sans-serif" color="#000000" align="center"}​​​​​​​​ metricFormat="0,0.[000]"
| render

```

However, the issue here is that when it reached a certain number of records, all of a sudden the query returned a wrong calculation.

Are there any limitations in Canvas that I am not aware of here?

Many thanks

---

<div class="post-metadata">

**Author:** ![markov00](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/markov00/32/33316_2.png) [@markov00](https://discuss.elastic.co/u/markov00)\
**Post date:** [July 22, 2021, 10:15am UTC](https://discuss.elastic.co/t/query-using-essql-conditional-case-function-missing-some-records/278552/2 "2021-07-22T10:15:47Z")

</div>

Hi, as described here: [SQL Limitations | Elasticsearch Guide [7.13] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-limitations.html#_sorting_by_aggregation) there are some limitations on the number of rows returned by ESSQL.  
What is the cardinality of the `Id` field?  
Probably you can transform your ESSQL query to:

```auto
SELECT COUNT(DISTINCT Id) as cnt FROM index where message = 'Success'

```

---

<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:** [August 19, 2021, 10:16am UTC](https://discuss.elastic.co/t/query-using-essql-conditional-case-function-missing-some-records/278552/3 "2021-08-19T10:16:28Z")

</div>

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