add data from current day to a qvd file

[reissmann]

Hi,

i have to work with a big data base with is not programmed very well. In former times i collected nearly 2.000.000 line of different tables (47) and analyzed them each 30 minutes.

Now i found the possibility to create qvd files and would like to use it for collect and analyze data at night. These data should be added be data of current day each 30 minutes. Is this possible. Can i store specified data to existing table?

Thanks.

[marcus_sommer]

You may follow an approach like this:

t: load *; sql select * from db.table where datefield = today();
concatenate(t) load * from t.qvd (qvd) where not exists(KEY);
store t into t.qvd (qvd);

You need to adjust today() to the appropriate date-function of your db. Further important is the use of a where [not] exists(FIELD) statement to keep the load from the qvd optimized.

In your case you may need to reverse the load-order and/or switch from a not exists() to an exists() and/or creating appropriate KEY- and/or DATE-fields ... It depends on various factors which approaches are in general working and which are more or less suitable as the other. The above is aimed to add new records to a historical source. If there also changed and/or deleted records it needs of course more efforts.

One more hint - often is the query in the db and the transfer of the data the biggest bottleneck. This means the sql query itself must be restricted and this could be quite difficult if you couldn't restrict the data with a date-field because restricting the data over a KEY means to specify the KEY values within an in() statement which is often restricted to < 1000 values - therefore the above mix of querying against today and against a KEY.

- Marcus   

[reissmann]

thank you very much, i will try this and give feedback 🙂

[Brett_Bleess]

@reissmann Another resource that may be helpful is the following Help Doc link on incremental load using QVD files, should reinforce what Marcus was saying I believe.  If Marcus' suggestion did work, do not forget to return to your thread and use the Accept as Solution Button on his post to mark that as the solution and give him credit as well as let others know that worked for you.

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/QVD_Incremental...

Regards,
Brett