Aggregation Query

[#1]

Hi,

I am trying to Aggregate a sample set of data. It looks something like;

Marks	Semester	Student	Subject
80	1	B	Eco
80	1	B	English
80	1	A	Eco
80	1	A	English
80	1	A	Maths
100	1	A	Main_Eco
100	1	A	Main_English
100	1	A	Main_Maths

The Requirement is to aggregate the set by Student such that any student ,if has at least one subject starting with'Main' , then the result should be Yes, else No, .Hence Student 'A' should be "Yes' and B be 'No'

I tried with the below expression, but the problem is it gives 'No' for Student 'A' with Non-Main subject row,

     if((Left(Subject,4)='Main')  ,If(Aggr(Sum(Marks), Student)>0,'Yes'),'No')

The result should look something like :

Student	Subject	Semester	Aggr
B	Eco	1	No
B	English	1	No
A	Eco	1	Yes
A	English	1	Yes
A	Main_Maths	1	Yes
A	Maths	1	Yes

Appreciate any help !

Thanks

[sunny_talwar]

Try this:

If(WildMatch(Aggr(NODISTINCT Concat(DISTINCT Subject), Student), '*Main*'), 'Yes', 'No')

Output:

[sunny_talwar]

Not sure if you need the second condition for any reason, you can add that in there too:

If(WildMatch(Aggr(NODISTINCT Concat(DISTINCT Subject), Student), '*Main*') and Aggr(NODISTINCT Sum(Marks), Student) > 0, 'Yes', 'No')

[maxgro]

if(max(TOTAL <Student> aggr(count({$ <Subject={"Main*"}>} Subject), Student, Subject, Semester))>0, 'yes', 'no')

[MarcoWedel · Accepted solution]

Hi,

another solution could be:

=If(Max(TOTAL<Student> WildMatch(Subject,'Main*')),'Yes','No')

hope this helps

regards

Marco

[MarcoWedel]

or

=If(Min(TOTAL<Student> Subject like 'Main*'),'Yes','No')

hope this helps

regards

Marco

[#7]

Thanks Marco !