Pre-calculating and storing aggregate data via QVD script

[#1]

Hey Everyone,

I have a bit of an 'is this possible' kind of question. Let me set it up for you:

I have two objects, let's call them Customers and Purchases. The Purchases carry the ID of the customers that they're related to.

I'm interested in two measures about this relationship. I want to know the recency, and frequency of the purchase activity for all of my customers. ie: I want to know how many purchases with a fixed time period, and I want to know how many days its been since the last one.

I can do this just fine within an object in the QVD, but it's expensive. For each of the customers I have to aggregate, group, and sum or count. But really, this is a static piece of information that isn't going to change, so... why calculate it over an over again?

can I implement this in the script? Or am I better off summarizing this using a view in SQL?

[prieper · Accepted solution]

You may create a summary-table and join with the customer-ID, like

LOAD
Customer_ID,
NOW-MAX(PurchaseDate) AS TimeElapsed,
COUNT(Purchase_ID) AS Transactions,
SUM(Purchase) AS Turnover,
SUM(Purchase) / COUNT(Purchase_ID) AS avrgTurnover,
...
RESIDENT
Purchases
WHERE
PurchaseDate > dDateFrom
GROUP BY
Customer_ID;

HTH
Peter