SET ANALYSIS SUBQUERY

[rulohx87]

Hello everyone,

I have a problem with a subquery. I want to get students who are deserters.

If the student has a status = "B *" is a defector, provided that does not exist in the same year with a Status P, I, C, NP, SC

This is my Query to obtain them.

Query Result "25.513" Defector

select distinct COUNT(*)

from STUDENT a

/* COUNT({<CiEglini = {2014}, EAGCve = {"B*"} >} STUDENT) */

/***RESULT = 49,536 defectors**  ------ How do I add the code in blue?

where     a.CiEgIni = 2014  and

            a.EAGCve like 'B%' and

            not exists (       select ''

                                from     STUDENT b

                                where     b.CiEgIni=a.CiEgIni and

                                            b.CURP=a.CURP and

                                            b.EAGCve in ('P','I','C','NP','SC'))

Is there another way?

[petter]

Do you look for another way in SQL or for another way with QlikView. Furthermore is it OK to do this in the QlikView application and not in the Load Script?

[petter · Accepted solution]

In a chart with Year and Student as a dimension you can use the following expression:

Sum(Aggr(-(Count({<Status={"B*"}>} Student)>0  AND Count( {<Status={'C','I','NP','P'}>} Student)=0),Year,Student))

So basically you Count the number of students that has status B* and then you make sure that they don't dont count if they have had the status C, I, NP or P within the same year.

If you don't need to collapse like in a Pivot Table so you always keep both dimensions active you could have a simplified expression like this:

-(Count({<Status={"B*"}>} Student)>0  AND Count( {<Status={'C','I','NP','P'}>} Student)=0)

This will return a 1 for each student that has deserted. This could be hidden in a Straight Table.

If you want to display the list of students in a Text Box you could use this expression:

=Concat({1<Year={2014},Status={'B'}>-<Student=P({<Year={2014},Status={'C','I','NP','P'}>})>} Student,Chr(10),Student ID)

I have attached a demo application where you can see both approaches being used.