Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How do I genereate one QVD file per day to export data?

Hello!

I have a question, the reload time to export data from a table that I have taken over 16  hours now and I need to speed it up. In QV it was possible to generate 1 QVD file per day to export data and then load the data from those files.

Is this possible in QS and if so, how do I write the code to acheive the generation with transactions for one day?

Also when loading data to build charts, how can I create one table with data from all the generated files? (how do I write the script for that)

This is the script I have to export the data today (I have just renamed customer details for databases and login accounts):

LIB CONNECT TO 'NAV 2009 (qlik_reader) (customeraccount_admin)';

SalesLineHist:

LOAD*;
SQL SELECT *
FROM "CustomerDB_2009_Test".dbo."Company$SalesLineHistory";

Store SalesLineHist into 'Lib://NAV Data (customeraccount_admin)/SalesLineHist.qvd' (qvd);

Drop table SalesLineHist;

8 Replies
miskin_m
Partner - Creator
Partner - Creator

Hi,

Why don't you try with incremental load instead of full load every time.

Please check on community for incremental load.

Regards,

Anonymous
Not applicable
Author

Hello!

I need to get all data first time at least. Incremental won't work for lets say the latest Changes over at least the past year so I would need to reload at least two years back all the time due to Changes in the table that might affect backwards.

But yes, I could example reload data intil like 2014-12-31 in one QVD file but from that Point on I would need to reqreate new files just be sure I will collect Changes made on old transactions. This is a statistics table which will be updated on several Points of events.

Vegar
MVP
MVP

You should look into incremental load as suggested by miskin.m‌‌, but I'll still try to answer your questions.

In QV it was possible to generate 1 QVD file per day to export data and then load the data from those files.

Is this possible in QS and if so, how do I write the code to acheive the generation with transactions for one day?

Yes it is possible in to do the same thing by script in Qlik Sense as in QlikView. I don't know your data, but your code should look somehting like this to achieve single day qvds.

LIB CONNECT TO 'NAV 2009 (qlik_reader) (customeraccount_admin)';

SalesLineHist:

LOAD *;
SQL SELECT *
FROM "CustomerDB_2009_Test".dbo."Company$SalesLineHistory"

WHERE DateField = cast (GETDATE() as DATE)

;

Store SalesLineHist into 'Lib://NAV Data (customeraccount_admin)/SalesLineHist_$(=date(today(),'YYYYMMDD')).qvd' (qvd);

Drop table SalesLineHist;

To load all files you could write something like this:

[Sales Line History]:

LOAD

*

FROM

'Lib://NAV Data (customeraccount_admin)/SalesLineHist_*).qvd (qvd)

;

Cheers

Vegar Lie Arntsen

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.


parimikittu
Creator II
Creator II

Hi, for your scenario, we should be using incremental load. Please check the below link to help you on incremenatal load. https://community.qlik.com/docs/DOC-9531

dsharmaqv
Creator III
Creator III

Append the date with file name if you dont want incremental load.

Store SalesLineHist into 'Lib://NAV Data (customeraccount_admin)/SalesLineHist_$(vDate).qvd' (qvd);

koushik_btech20
Creator
Creator

Try this ,

Let ThisExecTime = ReloadTime();

QV_Table:

SQL SELECT PrimaryKey, X, Y FROM DB_TABLE

WHERE ModificationTime >= #$(LastExecTime)#

       AND ModificationTime < #$(ThisExecTime)#;

Concatenate LOAD PrimaryKey, X, Y FROM File.QVD

WHERE NOT EXISTS(PrimaryKey);

Inner Join SQL SELECT PrimaryKey FROM DB_TABLE;

If ScriptErrorCount = 0 then

  STORE QV_Table INTO File.QVD;

  Let LastExecTime = ThisExecTime;

End If

Anonymous
Not applicable
Author

Thank you for your answer! will test and see what happens

miskin_m
Partner - Creator
Partner - Creator

Hi Jenny,

Yes you need to keep on reloading say last two years data. But then this won't take much of your time, i guess.