If I've imported a list of secondary schools and can get a list of all French teachers by selecting their job title, how do i get a list of schools that do not have a French teacher using the same query?
Also, is there a way to limit the number of French teachers at any one school (thrown out by the query) as many schools have several French teacher and I often only want 1 or 2.
If it is in the script, I would suggest use a flag for presence of French teacher.
Eg:
tableA:
Load school_name,
french_teacher_name,
job_title,
if(isnull(french_teacher_name), 1, 0) as flag_no_french_teacher
from file.qvd (qvd);
Now you can use the flag on your UI to filter such schools.
To achieve the same in the UI, you can create a list box with the expression:
if(isnull(french_teacher_name),school_name)
As for showing only a few names, it is easier to do so in a straight table on the UI. You will need to use dimension limit option (select show first two values). As for changes in the script, I can't think of any solution apart from using a group by
tableB:
load school_name,
maxstring(french_teacher_name) as french_teacher_1,
minstring(french_teacher_name) as french_teacher_2
