Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Use sum(DISTINCT Charge) instead of Aggr function
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.
Hi Ryan,
Does charge is equal across all invoice amounts(i.e all invoice amounts are same) for each billinglineid's
Regards
Neetha
Please post the sample app