topic Re: Re: Re: Re: Multiply values (instead of SUM) in QlikView

Multiply values (instead of SUM)

salto — Tue, 16 Sep 2014 05:33:14 GMT

Hello,

In the load script, apart from make the SUM of values of a field, I would like to multiply them.

Sum(Indice) is perfect to SUM the values grouped by IDField, but, is there any way to have the multiplication of all values of the "Indice" field grouped by IDField?

Table:

load

IDField,

Prod(Indice)

Sum(Indice) as [Indice Field]

resident Temp

Group by IDField;

Thanks!

Re: Multiply values (instead of SUM)

tresB — Tue, 16 Sep 2014 05:48:35 GMT

May be like attached sample.

Hope, someone can suggest a better solution.

Update: Loaded the right app.

Re: Re: Multiply values (instead of SUM)

MarcoWedel — Tue, 16 Sep 2014 06:18:12 GMT

Load Calls,
  Date(Date) as Date,
    Evaluate(Concat(Calls,'*')) as Product
  Group By Date, Calls;
Load * Inline [
Date,Calls
41640,5
41640,5
41640,5
41640,5
41671,5
41671,5
]

regards

Marco

Re: Multiply values (instead of SUM)

anbu1984 — Tue, 16 Sep 2014 06:43:36 GMT

Initial:

Load * Inline [

Date,Calls

41640,5

41671,5

] ;

NoConcatenate

Inter:

Load Date,If(Date <> Previous(Date),Calls,Calls*Peek(Calls_mult)) As Calls_mult Resident Initial Order by Date;

NoConcatenate

Target:

Load Date(Date), Max(Calls_mult) Resident Inter Group by Date ;

Drop table Initial;

Drop table Inter;

Re: Re: Multiply values (instead of SUM)

Tue, 16 Sep 2014 09:56:42 GMT

Look here:

Multiplication of aggregation results

Re: Re: Re: Multiply values (instead of SUM)

MarcoWedel — Tue, 16 Sep 2014 10:37:57 GMT

so in case, the multiply aggregation is needed more than once:

SET Mul = exp(Sum(log($1)));

Load Calls, 
  Date(Date) as Date, 
  $(Mul(Calls)) as Product 
  Group By Date, Calls; 
Load * Inline [ 
Date,Calls 
41640,5 
41640,5 
41640,5 
41640,5 
41671,5 
41671,5 
]

hope this helps

regards

Marco

Re: Re: Re: Re: Multiply values (instead of SUM)

MarcoWedel — Tue, 16 Sep 2014 23:02:25 GMT

extension for negative values:

SET Mul = exp(Sum(log(fabs($1))))*(2*Odd(Sum($1<0))+1);

tabCalls:
LOAD RecNo() as ID,
    Calls,
    Date(Date) as Date
Inline [
Date,Calls
41640,5
41640,5
41640,5
41640,5
41671,5
41671,5
41678,1
41678,2
41678,3
41678,4
41678,5
41685,1
41685,2
41685,-3
41685,4
41685,5
41692,1
41692,2
41692,-3
41692,4
41692,-5
41699,-1
41699,2
41699,-3
41699,4
41699,-5
41706,1
41706,2
41706,3
41706,4
41706,0
41713,1
41713,2
41713,-3
41713,4
41713,0
]  ;

tabMult:
LOAD Date,
  $(Mul(Calls)) as Product
Resident tabCalls
Group By Date;

hope this helps

regards

Marco