topic Re: Aggregation Query in QlikView

Aggregation Query

Sat, 27 Jun 2015 12:34:36 GMT

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

Re: Aggregation Query

sunny_talwar — Sat, 27 Jun 2015 12:46:57 GMT

Try this:

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

Output:

Re: Aggregation Query

sunny_talwar — Sat, 27 Jun 2015 12:49:55 GMT

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')

Re: Aggregation Query

maxgro — Sat, 27 Jun 2015 13:23:02 GMT

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

Re: Aggregation Query

MarcoWedel — Sat, 27 Jun 2015 20:22:39 GMT

Hi,

another solution could be:

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

hope this helps

regards

Marco

Re: Aggregation Query

MarcoWedel — Sat, 27 Jun 2015 20:27:12 GMT

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

hope this helps

regards

Marco

Re: Aggregation Query

Mon, 29 Jun 2015 11:11:25 GMT

Thanks Marco !