Canvas: visualize with two different fields within the same index

[emad101]

Hello,
I am trying to make a bar chart in Canvas with two fields. I want the Y-axis to have the unique count on 'filed A' and the X-axis to display the top six names in 'field B' that has the most unique count in 'field A' while also excluding some names from the count

I tried the following with ELastic SQL, but it does not seem to give me all the records and I can't limit it to 6

SELECT  component_name AS value, app_code AS appcode	 
FROM "index**"  WHERE NOT (app_code = 'xxxx' 
OR app_code = 'yyyy' OR app_code = 'zzzz' ) 

I am able to get the correct result with the visualization tab, but not in canves.
Any help will be appreciated?

[markov00 (Marco Vettorello)]

You should check https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-functions-aggs.html and apply a GROUP BY and an aggregation function into your code.
With the SQL provided you are just selecting all the documents you have, you instead have to group by your app_code, apply a count aggregation function, and then filter out with WHERE clause.
You can also add LIMIT 6 at the end of the query to limit directly on the SQL

[emad101]

Great, that was very helpful.
Thank you