Canvas filter dropdown empty

[aherabit (A)]

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.

[matriv (Marios Trivyzas)]

Do you mean like '%NA%'?

[aherabit (A)]

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

[matriv (Marios Trivyzas)]

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

[aherabit (A)]

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

[matriv (Marios Trivyzas)]

vNF.keyword is for the WHERE clause only.

[aherabit (A)]

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

[Luca_Belluccini]

Hello @aherabit

Can you try with:

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

[aherabit (A)]

thanks!