Canvas: visualize with two different fields within the same index

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?

You should check 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

Great, that was very helpful.
Thank you

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