topic Re: Aggregate data in QlikView

Aggregate data

deepti_singh — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

Please help me with the below requirement. I have data as below

Trade Payout Date Commission

A A1 07/06/2016 $50

A A2 07/06/2016 $75

A A3 07/06/2016 $35

B B1 08/06/2016 $50

B B2 08/06/2016 $25

B B3 09/06/2016 $75

C C1 09/06/2016 $50

C C2 09/06/2016 $50

C C3 10/06/2016 $15

The requirement is to show the data as follows, that means for each trade for a given date, show the sum of commissions for its payout that are less than or greater than $100. See how in the given example, the 4th row is not shown or suppressed.

A A1, A2, A3 07/06/2016 $160

B B1, B2 08/06/2016 $ 75

B B3 09/06/2016 $ 75

C C1,C2 09/06/2016 $100 (suppress or not show this row)

C C3 10/06/2016 $ 15

Thank you.

Deepti

Re: Aggregate data

Wed, 20 Jul 2016 00:30:23 GMT

Hi Deepti,

Please do the following steps.

load * inline
[
Trade , Payout, Date , Commission

A , A1 , 07/06/2016 , 50

A , A2 , 07/06/2016 , 75

A , A3 , 07/06/2016 , 35

B , B1 , 08/06/2016 , 50

B , B2 , 08/06/2016 , 25

B , B3 , 09/06/2016 , 75

C , C1 , 09/06/2016 , 50

C , C2 , 09/06/2016 , 50

C , C3 , 10/06/2016 , 15
];

Create a pivot Table

Choose Trade and Date as dimensions.

Expression1: Concat(Payout, ',')
Expression2: Sum(Commission)

Good luck.

Ram

Re: Aggregate data

Wed, 20 Jul 2016 00:32:42 GMT

To supress 100, modify expression2 as follows.

if(Sum(Commission)<> 100, Sum(Commission), 'NA')

Re: Aggregate data

sunny_talwar — Wed, 20 Jul 2016 01:31:57 GMT

This?

Re: Aggregate data

deepti_singh — Wed, 20 Jul 2016 17:13:10 GMT

It worked. Thanks Ramkumar.

Thanks.

Deepti

Re: Aggregate data

deepti_singh — Wed, 20 Jul 2016 18:02:03 GMT

It worked with this method as well. Thanks Sunny.

Thanks.

Deepti