topic Re: Data loading from QVD in QlikView

Data loading from QVD

Wed, 03 Jun 2015 10:35:35 GMT

Hello everyone,

So I'm starting to implement qvd in my qlikview application.

So far I have read some posts about how to use the qvds and I already created the files.

So far the script on my qvw has the following structure:

SQL Connection String

query1:

Select statement;

STORE query1 INTO query1.qvd(qvd);

query2:

Select statement;

STORE query2 INTO query2.qvd(qvd);

query3:

Select statement;

STORE query3 INTO query3.qvd(qvd);

The qvds are created with the data from the select statement. But I have some questions regarding this:

- How do the qvds reload the information?

- After the creation of the qvds, can I delete the select statement from the qvw?

- Should I put the select statement in the qvd instead?

I'm a little confused regarding how everything should be connected in order to be efficient using the qvds.

Thank you in advance.

Ivo

Re: Data loading from QVD

swuehl — Wed, 03 Jun 2015 10:41:56 GMT

- If you want the data in the QVD reloading the data from the DB, you need to rerun above script.

-Yes, you can, but then you will work with the data stored in the qvd, it's not possible to update data anymore.

-No.

Re: Data loading from QVD

Gysbert_Wassenaar — Wed, 03 Jun 2015 10:45:56 GMT

Qvd files are just data files. They cannot 'do' anything, so they'll never reload themselves. You create qvd files with a load script like you posted. Running that script again will recreate the qvd files with the latest data.

After you created the qvd files you can load the data from the qvd files instead of from your sql database. If that's what you want to do then you can remove the sql statements.

There's no way for you to put a select statement into a qvd file other than creating a table with an sql statement and store that table into the qvd file. The select statement that created the table is stored in the qvd in the xml header of the qvd document. But it's just metadata. It won't be used when you load the data from the qvd file.

Re: Data loading from QVD

swuehl — Wed, 03 Jun 2015 10:47:29 GMT

You can use a layered approach:

Build a QVW that queries your DB and stored the data into QVD.

Have that QVW reloaded as needed to keep QVD in sync with DB.

Have another QVW(s) use these QVD to build the data model.

You can set up security that users creating the second QVW can't access the first QVW, only have access to the deployed QVDs.

Re: Data loading from QVD

Wed, 03 Jun 2015 10:48:18 GMT

But there is one thing that is not making sense to me.

To update the qvds I have to execute the script in the qvw and I still have to wait for the select statements to finish, so how do I have a performance enhancement with the qvds like this?

Thank you for the reply

Re: Data loading from QVD

swuehl — Wed, 03 Jun 2015 10:53:59 GMT

In a simple setting, you won't save time for reloading your application.

But imagine reusing the same created QVDs in a lot of different QVWs (for Sales, Executive dashboard, Production etc.).

And you can run several QVWs querying the DB(s) in parallel.

Re: Data loading from QVD

Wed, 03 Jun 2015 10:54:45 GMT

Hi Ivo Marques

- QVDs are nothing but data files. (Same as Excel with some data in it)

- When you generate a qvd its nothing but extracting the data from database (currently available data in the database at time of loading) and saving it as copy.

- Each time you reload it will overwrite the data

- Your select statements are required all the time to read the data and store command to create qvd. so create one dedicated qvw file for this ( for example : qvd_generator.qvw ) and you can schedule this qvw file in the server to generate qvd files .

Regards

Veeki

Re: Data loading from QVD

Thu, 04 Jun 2015 12:39:42 GMT

Thank you for the help guys!

From what I understood I will always need a QVW to generate the QVD.

The QVD is only an advantage if I have multiple QVW accessing the same data source.

For my application I only need to have one QVW so, there is not really any advantage in using the QVD...

I have 4 sql queries running in my application. The application takes about 3/4 minutes to reload the data and I was studying the QVD in order to reduce the reload time. But unfortunately it does not seem to be the most correct thing to use.

Are there any optimization techniques that can improve the reload time?

Thank you once again for the help.

Re: Data loading from QVD

ankit777 — Thu, 04 Jun 2015 13:00:17 GMT

You must try incremental loading.

Re: Data loading from QVD

Fri, 05 Jun 2015 09:29:40 GMT

Well after some test with incremental loading, I was able to reduce a query of 1 minute to 7 seconds.

Not bad

Still I have one problem. I have four SQL queries and so I am creating 4 different QVDs for each query and for each I have 5 STORE statements:

STORE query1 INTO query1.qvd(qvd);

STORE query2 INTO query2.qvd(qvd);

STORE query3 INTO query3.qvd(qvd);

STORE query4 INTO query4.qvd(qvd);

Is there a way of writing the four queries to the same QVD?

Something like: STORE query1, query2, query3, query4 INTO GeneralQuery.qvd(qvd);

Thank you for the help!

Re: Data loading from QVD

ankit777 — Fri, 05 Jun 2015 09:32:14 GMT

If all your tables have same columns and similar data u can concatenate it in one table and store in one qvd.

Re: Data loading from QVD

qlikmsg4u — Fri, 05 Jun 2015 09:35:12 GMT

QVD stores only one table, if you want to store more than one table then you need to concatenate those tables .

Please refer this link for more information on this topic

https://community.qlik.com/thread/28747

Re: Data loading from QVD

Fri, 05 Jun 2015 12:22:46 GMT

I cannot find information about how to join the queries.

I made a little bit of research but everything I find is not exactly what I need.

My script has the following structure:

query1:

Select statement;

query2:

Select statement;

query3:

Select statement;

query4:

Select statement;

When the script reloads all the result are joined because I have the same ID in all the selects of the 4 queries.

My problem is how I can join everything in a general query to send to a QVD.

I was trying to do this:

GeneralQuery:

LOAD * RESIDENT query1; OUTER JOIN(GeneralQuery) LOAD * RESIDENT query2;

GeneralQuery:

LOAD * RESIDENT GeneralQuery; OUTER JOIN(GeneralQuery) LOAD * RESIDENT query3;

GeneralQuery:

LOAD * RESIDENT GeneralQuery; OUTER JOIN(GeneralQuery) LOAD * RESIDENT query4;

But my script returns a error of table not found (I think is because of the "GeneralQuery"), but I do not know how to solve it... Am I missing something in the script?

Thank you for the help.

Re: Data loading from QVD

Gysbert_Wassenaar — Fri, 05 Jun 2015 14:45:21 GMT

Perhaps one or more of the queryN tables is not created. If a table contains exactly the same fields as a table created earlier then the data is appended to that existing table instead of creating a new table.

Re: Data loading from QVD

Anonymous — Fri, 05 Jun 2015 14:47:00 GMT

"From what I understood I will always need a QVW to generate the QVD. "

INCORRECT

Re: Data loading from QVD

ankit777 — Mon, 08 Jun 2015 07:50:45 GMT

What I mentioned was , if all your query return same columns then u can use concatenate statement to concatenate all query result into one table and then save as one QVD.

You are getting an error because while using resident load prefer adding NOCONCATENATE before the load statement(if columns are same, then QLikview autoconcats the tables)