How to STORE data into a QVD file daily

[MEJ]

Hi all,

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. 

Thanks

[siddheshmane]

Hi,

Is the data coming from excel ? or do you use any database?

[MEJ]

Yes, the data is coming from Excel.

[siddheshmane]

You can use the Store command and append a timestamp to the filename.
Like - STORE Tablename into Tablename_$(vToday).qvd (qvd);

You will need to create a variable vToday that holds the timestamp.

[MEJ]

Thanks,

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?

[siddheshmane]

You will have to mention all the fields that you need to store in the Qvd in the STORE command.
STORE Field1, Field2, Field3 as FieldA FROM Table into [qvd path].

[MEJ]

Does the dollar sign have any meaning as to the way it is saving? 

Tablename_$(vToday).qvd (qvd);

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?

[siddheshmane]

The dollar sign is a means to get the value from the variable vToday. if you just write vToday, the file name will be saved as Tablename_vToday and not the Date.

[sayadutt]

Hi,

did you find solution on how to avoid hundreds of files getting created?

please let me know. I am also facing the same problem.

thanks

[siddheshmane]

Hi Sayadutt,

Did you try using the above method?