topic Intersection between 2 values in QlikView

Intersection between 2 values

Edge — Sat, 16 Nov 2024 00:39:40 GMT

Hey guys,

I have the below dataset:

Student ID, Semester

1, A
2, A
2, B
3, B

I am using the below expression to give me the count of the common items which should be 1 since only student 2 attened semester A & B. But it is giving me 4. Can someone help please?

=Count(

{1<Semester=,[Academic Year]=,SemesterNO={'$(=max(SemesterNO))'}> +
1<Semester=,[Academic Year]=,SemesterNO={'$(=max(SemesterNO)+100)'}>}
DISTINCT Enrolment.EMPLID)

Re: Intersection between 2 values

Or — Sun, 17 May 2020 13:33:16 GMT

You seem to be using the '+' operator, which is union, rather than the '*' modifier for intersect.

Re: Intersection between 2 values

Taoufiq_Zarra — Sun, 17 May 2020 13:33:24 GMT

@Edge

can you share a sample data

SemesterNO ? Academic Year ? Semester ?

what is the expected output ? from this output :

Student ID, Semester

1, A
2, A
2, B
3, B

Re: Intersection between 2 values

Saravanan_Desingh — Sun, 17 May 2020 18:41:52 GMT

To get the value:

=Only({<[Student ID]=p({<Semester={'A'}>})*p({<Semester={'B'}>})>}[Student ID])

To get the count:

=Count(DISTINCT {<[Student ID]=p({<Semester={'A'}>})*p({<Semester={'B'}>})>}[Student ID])

Re: Intersection between 2 values

Saravanan_Desingh — Sun, 17 May 2020 19:03:01 GMT

One more solution.

=Count(DISTINCT{<[Student ID]={"=Concat(DISTINCT Semester)='AB'"}>}[Student ID])

Re: Intersection between 2 values

Edge — Tue, 19 May 2020 07:18:20 GMT

Thanks to all who responded and provided solutions :). Really appreciate the amazing qlik community.