# Canvas filter dropdown empty

**URL:** <https://discuss.elastic.co/t/canvas-filter-dropdown-empty/231376>\
**Category:** Elasticsearch\
**Tags:** canvas\
**Created:** [May 6, 2020, 3:47pm UTC](https://discuss.elastic.co/t/canvas-filter-dropdown-empty/231376 "2020-05-06T15:47:51Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![aherabit](https://avatars.discourse-cdn.com/v4/letter/a/f07891/32.png) [@aherabit](https://discuss.elastic.co/u/aherabit)\
**Post date:** [May 6, 2020, 3:47pm UTC](https://discuss.elastic.co/t/canvas-filter-dropdown-empty/231376/1 "2020-05-06T15:47:51Z")

</div>

Hi Folks. The filter element has a SQL query that shows valid and grouped results but the drop-down filter show nothing but '-- ANY --'

```
essql query="SELECT vNF FROM \"train_nc_e2e\" where vNF not like 'NA' group by vNF"
| dropdownControl valueColumn="vNF" filterColumn="vNF"
| render

```

I have one page in the canvas with several elements (charts & metrics) and time/date filter that work.

thank you in advance.

---

<div class="post-metadata">

**Author:** ![matriv](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/matriv/32/43656_2.png) [@matriv](https://discuss.elastic.co/u/matriv)\
**Post date:** [May 8, 2020, 11:14am UTC](https://discuss.elastic.co/t/canvas-filter-dropdown-empty/231376/2 "2020-05-08T11:14:57Z")

</div>

Do you mean `like '%NA%'`?

---

<div class="post-metadata">

**Author:** ![aherabit](https://avatars.discourse-cdn.com/v4/letter/a/f07891/32.png) [@aherabit](https://discuss.elastic.co/u/aherabit)\
**Post date:** [May 8, 2020, 2:04pm UTC](https://discuss.elastic.co/t/canvas-filter-dropdown-empty/231376/3 "2020-05-08T14:04:58Z")

</div>

Thanks.  
The query removes 'NA' from the selection.  
There are actual 'NA' entries in the DB records.

---

<div class="post-metadata">

**Author:** ![matriv](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/matriv/32/43656_2.png) [@matriv](https://discuss.elastic.co/u/matriv)\
**Post date:** [May 8, 2020, 2:21pm UTC](https://discuss.elastic.co/t/canvas-filter-dropdown-empty/231376/4 "2020-05-08T14:21:44Z")

</div>

Is `vNF` a text or keyword?  
consider using `vNF.keyword` in your filter.

---

<div class="post-metadata">

**Author:** ![aherabit](https://avatars.discourse-cdn.com/v4/letter/a/f07891/32.png) [@aherabit](https://discuss.elastic.co/u/aherabit)\
**Post date:** [May 8, 2020, 2:43pm UTC](https://discuss.elastic.co/t/canvas-filter-dropdown-empty/231376/5 "2020-05-08T14:43:50Z")

</div>

Thanks...  
Whoops! Expression failed  
Expression failed with the message:  
[essql] \> Unexpected error from Elasticsearch: [verification\_exception] Found 1 problem(s) line 1:8: Cannot use non-grouped column [vNF.keyword], expected [vNF]

> 'eessql  
> query="SELECT vNF.keyword FROM "ecomp\_nc\_e2e" where vNF not like 'NA' group by vNF"  
> | dropdownControl valueColumn="vNF" filterColumn="vNF.keyword"  
> | render'

and no change with the following:

> essql query="SELECT vNF FROM "ecomp\_nc\_e2e" where vNF not like 'NA' group by vNF"  
> | dropdownControl valueColumn="vNF.keyword" filterColumn="vNF.keyword"  
> | render

---

<div class="post-metadata">

**Author:** ![matriv](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/matriv/32/43656_2.png) [@matriv](https://discuss.elastic.co/u/matriv)\
**Post date:** [May 8, 2020, 3:06pm UTC](https://discuss.elastic.co/t/canvas-filter-dropdown-empty/231376/6 "2020-05-08T15:06:34Z")

</div>

vNF.keyword is for the WHERE clause only.

---

<div class="post-metadata">

**Author:** ![aherabit](https://avatars.discourse-cdn.com/v4/letter/a/f07891/32.png) [@aherabit](https://discuss.elastic.co/u/aherabit)\
**Post date:** [May 8, 2020, 3:21pm UTC](https://discuss.elastic.co/t/canvas-filter-dropdown-empty/231376/7 "2020-05-08T15:21:57Z")

</div>

thanks matriv.

still same behavior. That is query returning valid list of data but filter drop-down displays nothing by '--- ANY ---'.

> essql  
> query="SELECT vNF FROM "ecomp\_nc\_e2e" where vNF.keyword not like 'NA' group by vNF"  
> | dropdownControl valueColumn="vNF" filterColumn="vNF"  
> | render

---

<div class="post-metadata">

**Author:** ![Luca\_Belluccini](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/luca_belluccini/32/33239_2.png) [@Luca\_Belluccini](https://discuss.elastic.co/u/Luca_Belluccini)\
**Post date:** [May 15, 2020, 12:22am UTC](https://discuss.elastic.co/t/canvas-filter-dropdown-empty/231376/8 "2020-05-15T00:22:17Z")

</div>

Hello @aherabit

Can you try with:

```auto
essql
query="SELECT vNF FROM \"ecomp_nc_e2e\" where vNF <> 'NA' AND vNF IS NOT NULL group by vNF"
| dropdownControl valueColumn="vNF" filterColumn="vNF"
| render

```

---

<div class="post-metadata">

**Author:** ![aherabit](https://avatars.discourse-cdn.com/v4/letter/a/f07891/32.png) [@aherabit](https://discuss.elastic.co/u/aherabit)\
**Post date:** [May 27, 2020, 7:50pm UTC](https://discuss.elastic.co/t/canvas-filter-dropdown-empty/231376/9 "2020-05-27T19:50:23Z")

</div>

thanks!

---

<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:** [June 24, 2020, 7:50pm UTC](https://discuss.elastic.co/t/canvas-filter-dropdown-empty/231376/10 "2020-06-24T19:50:27Z")

</div>

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