Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am looking for set analysis that can get me the count of students from the below table.
ID, Subject
1, Maths
1, Science
2, Maths
2, Chemistry
3, Maths
Below are the 2 expressions I need some help.
1. I need to calculate the count of students who have taken only Maths - In this case the student ID 3 has taken only Maths. So the count I am looking for is only 1.
2. I need to calculate the count of students who have taken Maths and Science - In this case, the student 1 has taken both Maths and Science. So the count I am looking for is only 1.
Any help is much appreciated.
Thanks
Sarathi
Script
NoConcatenate
[StudentCourseData]:
LOAD * Inline [
ID, Subject
1, Maths
1, Science
2, Maths
2, Chemistry
3, Maths
];
NoConcatenate
[NumOfCoursesByStudent]:
LOAD
ID,
Count(DISTINCT Subject) as NumOfCourses
Resident [StudentCourseData]
Group By ID;
Expressions
='Only Math' & chr(10) & Count({<NumOfCourses={1}, Subject={'Maths'}>} DISTINCT ID)
='Math & Science' & chr(10) & Count({<NumOfCourses={2}, Subject={'Maths'}, Subject={'Science'}>} DISTINCT ID)
='Math & Science' & chr(10) & 'Student ID' & chr(10) & Max({<NumOfCourses={2}, Subject={'Maths'}, Subject={'Science'}>} DISTINCT ID)
In table
If(NumOfCourses = 1 and Subject = 'Maths', 'Only Math Yes', 'Only Math No')
Can you tell me which product this is for I'd like to move it to the correct forum. Thank you! @sarathi_pm @vvira1316
Can you tell me which product this is for I'd like to move it to the correct forum. Thank you! @sarathi_pm @vvira1316
Only Maths: count({<Subject={'Maths'},ID={"=count(distinct Subject)=1"}>}distinct ID)
Maths and Science: count({<Subject={'Maths'}>*<ID=P({<Subject={'Science'}>}ID)>} distinct ID)