Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
salto
Specialist II
Specialist II

Multiply values (instead of SUM)

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!

1 Solution

Accepted Solutions
tresesco
MVP
MVP

May be like attached sample.

Hope, someone can suggest a better solution.

Update: Loaded the right app.

View solution in original post

6 Replies
tresesco
MVP
MVP

May be like attached sample.

Hope, someone can suggest a better solution.

Update: Loaded the right app.

MarcoWedel

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

anbu1984
Master III
Master III

Initial:

Load * Inline [

Date,Calls

41640,5

41640,5

41640,5

41640,5

41671,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;

Not applicable

MarcoWedel

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

MarcoWedel

extension for negative values:

QlikCommunity_Thread_133463_Pic1.JPG.jpg

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