Hi, I'm trying to show ratio of active vs total count of students by school in a map. Whereever the ratio is less than 50% need to color that bubble by red and green otherwise.
The query im using :
|inputlookup qa.csv | table student_id, school_name| stats dc(student_id) as total by school_name | join type=left school_name [search index="qa_xapi" | table student_id,school_name| stats dc(student_id) as active by school_name] |fillnull active value=0 | eval ratio=(active/total)*100 | table school_name, ratio
Now qa.csv file contain lat,long information but the qa_xapi index doesn't contain lat,long field. The index contain active users where the lookup has total users dump so we need a left join to get active/total ratio.
So how can I bring this ratio correlation with lat,long and use geom or geostats to render this in a map. If this can be solved 90% is done then we can render the color's based on ratio using eval condition.
Also if you can suggest any other solution other than left join for this.
