Storing statistics of large raw data into QVDs

[sifatnabil]

Hi all,

I have a script that polls a login server 4 times an hour and generates approx. 200,000 rows per day, which is all stored into a QVD.

Since the QVD will become too large in size, I wish to delete the QVD after 3 months. However, I want to keep the statistics that can be calculated from the raw QVD, e.g. daily average of users logged in, monthly average, etc.

I have a few questions:

1. How do I calculate averages in the script?

2. If I want to calculate and store statistics once every day, how do I make the statistics store statement run once a day while the raw QVD store statement runs every time the doc is loaded? e.g. STORE [statisticstable] INTO [QVDs\statisticstable.qvd] (qvd);

3. How can I delete the raw QVD file automatically every 3 months?

Thanks for your help.

[marcus_sommer · Accepted solution]

1. You need aggregation-loads like:

daily:

Load date, count(LoggedIn) as LoggedInDaily From xyz group by date;

2. You could use a if-loop with a counter like:

     if the counter = 96 (24 * 4) then

          Load + store + counter = 0

     else

          Nothing

     end if

3. see here: QlikTip #30: How to delete existing QVD files via load-script

- Marcus