topic Re: How do I genereate one QVD file per day to export data? in Visualization and Usability

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

Anonymous — Wed, 04 Jan 2017 07:31:47 GMT

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;

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

miskin_m — Wed, 04 Jan 2017 08:12:32 GMT

Hi,

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

Please check on community for incremental load.

Regards,

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

Anonymous — Wed, 04 Jan 2017 08:17:13 GMT

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.

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

Vegar — Wed, 04 Jan 2017 08:31:09 GMT

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.

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

parimikittu — Wed, 04 Jan 2017 08:34:36 GMT

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

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

dsharmaqv — Wed, 04 Jan 2017 08:35:40 GMT

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

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

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

koushik_btech20 — Wed, 04 Jan 2017 08:50:56 GMT

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

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

Anonymous — Wed, 04 Jan 2017 08:57:06 GMT

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

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

miskin_m — Thu, 05 Jan 2017 05:58:24 GMT

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.