Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

sum, aggr and count

Hi All,

I have the below expression in my qlikview script and would like to know how we can reproduce the same in SQL so that i can validate the numbers at the backend.

Sum(Aggr(Count( DISTINCT ProductCode), ProductCode, Division))

I understand that AGGR function will work like a group by function in SQL. The output of Aggr(Count( DISTINCT ProductCode), ProductCode, Division) will be the count of productcode grouped by productcode and Division.

Thank you.

Regards,

Viresh

2 Replies

Re: sum, aggr and count

May be this

SELECT SUM(COUNT) AS SUM

FROM (

SELECT Count(DISTINCT ProductCode) AS COUNT,

     ProductCode,

     Division

FROM ....

Group By ProductCode, Division);

Highlighted
MVP
MVP

Re: sum, aggr and count

This expression does not make sense. The Count() will be one for every ProductCode/Division combination (and the DISTINCT will do nothing since you are already distinct because of the Aggr() dimensions). You cannot do this expression directly in SQL as SQL will not allow you to aggregate and group by the same field. The nearest I could get was

SELECT Count(*)

FROM  ...

Group By ProductCode, Division;

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein