I am struggling to find a solution to my problem.
Today I have a Qlikview application that shows the backlog of all future orders. A backlog is only a snapshot of all current orders. New orders come in everyday, and other orders are disappearing from the backlog on a daily basis as well (invoiced).
In order to go back to a certain historical date to view what the backlog looked like then, I would need to save today's backlog every day (today I export the backlog into Excel and saves it into an Excelfile that keeps growing as time passes, and then reload the application to pick up those new records). Approximately 4000 records are added every day (Monday to Friday). After 1 year it will be almost 1 million records. Not a huge problem as I can create new Excel files, once they become to big, but the main problem is that I have to do this manually (it takes 2 minutes per day), and if I am not here, then it will not get done, and that statistics will be gone forever, as it is not possible to recreate a backlog.
So, I wonder if it is possible to use the "store" command to save it into the same QVD file each day so it keeps adding the daily backlog with a timestamp (2019-05-27 is good enough).
The data is coming our ERP system (query that is downloaded each night to Excel). Ideally I would of course read straight from the ERP system's database, but as we are not allowed to do that I have used the option to read from the downloaded Excel files. Not a big problem (in some cases it is even good, as it is possible to amend the data easily in Excel in case of data quality issues, compared to amending the data manually in our ERP system, which would have needed conversion of data from time to time).
Hope I explained it enough.
In my database, I have approximately 120 fields, but only 40 of them I need to be stored in the QVD file.
I also need to rename the field names with a prefix so that they don't interfere with the other fields in the database.
How would I select which of the fields goes into the QVD file? Today I have created a Straight table and renamed the field names with a "BL_" as a prefix, then saving it in an Excel file.
The Straight table report has an Object ID. Is it possible to store that object into the QVD file?
Does the dollar sign have any meaning as to the way it is saving?
It seems as though it creates one QVD file per date. I would prefer not to have hundreds of files per year, rather that each load is adding to an existing QVD file with new data, where "today's date" would be the separator.
If that is not possible, how can I create a load script that would load all created QVD files with different names (date being the separator)? Is it possible to use a "wildmatch" in the loadscript?