distinct calculation without using Aggr() - Performance

[#1]

I have an application where I am trying to improve performance.

Initially I had two separate tables:

Timesheets (2.4 million rows):

BillingLineID	Charge	ClientID	Other columns
12345678	120	123
87654321	80	456
45678912	110	789
76543219	200	234

Billing (2.7 Million rows)::

BillingLinedID	Invoice Amount	InvoiceDate	Others etc
12345678	40	1/1/2015
87654321	80	1/1/2015
12345678	40	1/2/2015
12345678	40	1/3/2015

As part of my charts and calculations throughout my application I am performing a Sum(Charge) and Sum(InvoiceAmount) based on different clients, dates etc. Lots of calculations.  These two charts are linked in BillinLineID.  As an example if I want to find out the total billed for a customer.  A client is selected in my application, this limits the results in the Timesheets Table, which then brings in the correct results from my Billing Table. 

To improve performance I am able to bring the data into QlikView in one table or perform a join in my Laod script based on BillingLineID.  The problem this causes is I now have a table which looks like this:

BillingLineID	Charge	InvoiceAmount	ClientID	InvoiceDate
12345678	120	40	123	1/1/2015
12345678	120	40	123	2/1/2015
12345678	120	40	123	3/1/2015
87654321	80	80	456	1/1/2015

So now obviously when I perform a sum(Charge) based on the clientID of 123 I receive a total of 360 instead of 120.  I was able to fix this by using the following expression sum(aggr(Charge,BillingLineID)) However I am using this for lots of calculations throughout my application and believe it will not be good for performance.

I would really appreciate any suggestions on how to meet my needs in a way which will have the best performance.

Thank you,

Ryan

[Anonymous]

hi Ryan,

use below expression:

sum(aggr( sum(InvoiceAmount),BillingLineID))

or

drop charge field while joining

Load

BillinLineID,

sum(InvoiceAmount) as Charge

from.....

Group by BillinLineID;

and use this charge field for calculations

Regards

Neetha

[#3]

Hi Neetha,

The formula sum(aggr(Charge,BillingLineID)) is what I am currently using and it works fine, however I don't want to have to use the Aggr function due to performance issues, it seems to take a long tim to calculate.

Also I should mention there are lots of other fields in the tables used and the charge doesn't always have a bill associated with it i.e. if a timesheet has been completed, this will create a value in charge, however there will not be a value in invoice amount until the work has actually been invoiced.  Sometimes it never gets invoiced and there is another field called WrittenOffValue if the work gets written off.

I hope this extra detail helps.

Ryan

[Kushal_Chawda]

Use  sum(DISTINCT Charge) instead of Aggr function

[#5]

This will not work as I want to do a sum of charge but based on the BillingLineID being the same.  i.e. there will be loads of instances of the charge equaling 40 across different timesheet entries.

[Anonymous]

Hi Ryan,

Does charge is equal across all invoice amounts(i.e all invoice amounts are same) for each billinglineid's

Regards

Neetha

[Kushal_Chawda]

Please post the sample app