topic Sum(distinct values) in QlikView

Sum(distinct values)

dandaanilreddy — Mon, 11 Apr 2016 22:52:49 GMT

Hello Qlik Developers,

I have a requirement. i need to combine two same fields as a single field by doing the sum of the price for the same values. Please see the requirement below. Distinct will remove the duplicates but i dont know how to sum the 2 records price. Can anyone help me please? Thanks in advance

Account data source 1	Account data source 2	symbol1	symbol 2	Month end date1	Month end date 2	Price 1	price 2
1234	1234	abc	abc	3/30/2016	3/30/2016	100	200
1234	1234	abc	abc	3/30/2016	3/30/2016	100	200
Final Result
Account data source 1	Account data source 2	symbol1	symbol 2	Month end date1	Month end date 2	Price 1	price 2
1234	1234	abc	abc	3/30/2016	3/30/2016	200	200

Re: Sum(distinct values)

Anonymous — Tue, 12 Apr 2016 01:56:39 GMT

Hi Anil,

You can add a straight table and add all your fields as dimensions except for Price 1 and Price 2, and add the sum of both values as an expression, this will create a row like the one in your example:

But, I'm not really sure if this fulfill your requirement, maybe you could provide more details and a more extended data set to understand your need better.

Regards,

-- Karla

Re: Sum(distinct values)

settu_periasamy — Tue, 12 Apr 2016 03:18:39 GMT

May be like this in script?

T1:
LOAD * INLINE [
Account data source 1, Account data source 2, symbol1, symbol 2, Month end date1, Month end date 2, Price 1, Price 2
1234, 1234, abc, abc, 3/30/2016, 3/30/2016, 100, 200
1234, 1234, abc, abc, 3/30/2016, 3/30/2016, 100, 200
];

NoConcatenate
T2:
LOAD [Account data source 1]
, [Account data source 2]
, [symbol1]
, [symbol 2]
, [Month end date1]
, [Month end date 2]
, Sum([Price 1]) as [Price 1]
, Sum([Price 2]) as [Price 2]
Resident T1 Group by [Account data source 1]
, [Account data source 2]
, [symbol1]
, [symbol 2]
, [Month end date1]
, [Month end date 2];

DROP Table T1;

Re: Sum(distinct values)

Tue, 12 Apr 2016 05:01:17 GMT

Hi Anil,

Try like this

T1:

LOAD * INLINE [

Account data source 1, Account data source 2, symbol1, symbol 2, Month end date1, Month end date 2, Price 1, Price 2

1234, 1234, abc, abc, 3/30/2016, 3/30/2016, 100, 200

];

NoConcatenate

T2:

load

[Account data source 1], [Account data source 2], [symbol1], [symbol 2], [Month end date1], [Month end date 2], [Price 2],

sum([Price 1]) as [Price 1]

Resident T1 Group by [Account data source 1], [Account data source 2], [symbol1], [symbol 2], [Month end date1], [Month end date 2], [Price 2];

DROP Table T1;

Note:

Try that we will get correct result what u want.