First sorted value or distinct value by partition ?

Mon, 17 Oct 2011 07:34:26 GMT

Hello,

I would like to make a sum on first values or distinct values on different partitions in a QVlogic table.

For exemple my logic table contain :

C1 C2 C3

1 1 18

1 2 18

1 3 19

I want the sum of the distinct C3 of the partition C1,C2. The value of C3 is the same on each the partition C1,C2.

If I write sum(distinct C3) i will lost a 18 in my sum...

In SQL Oracle I could write :

SELECT SUM(C4)

FROM

(SELECT case when (RANK() OVER (PARTITION BY C1,C2 ORDER BY C3))=1 then C3 END as C4

from

(SELECT '1' as C1,'1' as C2,'18' as C3 FROM DUAL UNION

SELECT '1','1','18' FROM DUAL UNION

SELECT '1','2','18' FROM DUAL UNION

SELECT '1','3','19' FROM DUAL UNION

SELECT '1','3','19' FROM DUAL))

Any idea for my QV expression ?

Mon, 17 Oct 2011 09:21:55 GMT

This should work: sum(aggr(sum(distinct C3),C1,C2))

Regards,

Kiran.

Mon, 17 Oct 2011 09:42:16 GMT

It's all right, thanks a lot !

topic First sorted value or distinct value by partition ? in QlikView