Hi All,
Scenario:
We have a pre-existing extract QVW that runs at intervals of 15mins 24/7 but this extract only pulls the last 14 days worth of data.
This is done in this way as a result of the sheer size of the files and performance of the server, as currently the performance is a bit compromised.
There is often times a requirement from management to see more than 14days worth of data for analytical purposes. So what I would like to accomplish is to keep a historical QVD file on my personal PC and when needed present this to management.
My question is two-fold:
lets say I copy the QVD from the server every few days, obviously the QVD's will then include the majority of the same information.
If i were to load all the QVD's into a QVW and use the store function (which will then obviously concatenate the tables), would this create duplicate values?
Furthermore, ideally, i would like to run a script on the server itself that can automatically save this data periodically, lets say at 12 at night everyday. How would I ensure that no duplicates are stored (keep in mind that i do have a Timestamp field in the data) so I imagine loading distinct would be adequate, ,however, the file would exceed 20million rows of data within a week and consist of about 30 columns of data, thus my concern is really the performance thereof.
The table mentioned above I will not include for the purpose of the question, instead I will give an example of a much smaller tables fields.
Script:
LOAD MANDT,
LGBEL,
TASKS
AREA,
QUEUE,
WHO,
CONFIRMATION_TIME
FROM
[..\..\QVLive\ORDIM_C.QVD]
(qvd);
Can I ask for some assistance with this regard?
Below is an example of what I had in mind, my concern is just that the variable will look at the MAX(CONFIRMATION_TIME) of the final concatenated table instead of the initial loaded table, which would result in the table not adding any new data.
My proposed solution:
LET vMaxdate = max(CONFIRMED_AT);
STOREDQVD:
LOAD
MANDT,
LGBEL,
TASKS
AREA,
QUEUE,
WHO,
CONFIRMATION_TIME
FROM
[..\..\QVData\QVLive\StoredQVD.QVD]
(qvd);
ORIGINALQVD:
LOAD
MANDT,
LGBEL,
TASKS
AREA,
QUEUE,
WHO,
CONFIRMATION_TIME
FROM
[..\..\QVData\QVLive\OriginalQVD.QVD]
(qvd)
where CONFIRMATION_TIME>$(vMaxdate);
STORE STOREQVD INTO StoredQVD(qvd);
