topic Re: Add data to qvd instead of replace it in QlikView

Add data to qvd instead of replace it

Fri, 13 Jun 2014 16:27:24 GMT

Hi. In my extract I am loading a bunch of data from a database into a qlikview table. After the table is created I am storing it into a qvd. I'm running into an issue that every time I run the extract the data replaces the previous data into the qvd. Is there code that will add the new data to the qvd and retain the old data? Currently I am using STORE * FROM Table INTO Table.qvd. I figured there might be a different code that I could use to do this. Thanks.

Re: Add data to qvd instead of replace it

Gysbert_Wassenaar — Fri, 13 Jun 2014 16:32:51 GMT

Nope. A qvd file is basically a memory dump of a table from qlikviews in-memory database. It's not possible to incrementally store to a qvd file. But since it is a memory dump it will be written to disk very quickly. What you can do is incremental loading and store each incremental extract into a separate qvd. Of course you then have to load all qvd's into qlikview to get the complete table.

Re: Add data to qvd instead of replace it

Fri, 13 Jun 2014 16:36:35 GMT

Say I create a different qvd for each day that I run the extract - is there a simple way to combine all of them together?

Re: Add data to qvd instead of replace it

Gysbert_Wassenaar — Fri, 13 Jun 2014 16:39:33 GMT

Yes, wildcard loading:

Table1:

Load X,Y,Z

from *.qvd (qvd);

Re: Add data to qvd instead of replace it

Clever_Anjos — Fri, 13 Jun 2014 16:40:46 GMT

Basically you can rewrite your QVD every time you run

LOAD

FROM [yourqvd.qvd](qvd);

concatenate

SQL

[your query goes here]

store t into [yourqvd.qvd](qvd);

Check your manual for "Incremental Load"

Re: Add data to qvd instead of replace it

Fri, 13 Jun 2014 16:45:15 GMT

I have tried this technique, but for some reason it messes up my data model. It only works for the first qvd in the directory, and then for the other ones it seems like the joins and concatenations are not working. Any idea how I can fix this?

Re: Add data to qvd instead of replace it

Gysbert_Wassenaar — Fri, 13 Jun 2014 16:47:08 GMT

The qvd's that should end up as one table should have exactly the same number of fields with the exact same field names.

Re: Add data to qvd instead of replace it

Fri, 13 Jun 2014 17:04:40 GMT

They definitely do because I am specifying the field names in the extract and the data model. Something is happening in the data model where it works for the first qvd, but doesnt for the other ones. I am using code in the data model like this:

Table1:

LOAD

Field1,

Field2

FROM

Table1\*.qvd (qvd);

Table2:

Left Join (Table1)

Field1,

Field3

FROM

Table2\*.qvd (qvd)

Any idea on what I'm doing wrong? Thanks.

Re: Add data to qvd instead of replace it

Gysbert_Wassenaar — Fri, 13 Jun 2014 17:11:58 GMT

If I remember correctly what happens is something like this:

After creating Table1 the first qvd for table2 gets joined to Table1 which adds the field Field3 to Table1. Then next qvd is now joined not on just Field1, but Field1 and Field3. Etc, etc.

Re: Re: Add data to qvd instead of replace it

rwunderlich — Fri, 13 Jun 2014 17:16:20 GMT

WIldcard loads don't work properly for JOINS. You have to load all the Table2 QVDs into a temp table and then join that to Table1. Like this.

Table1:

LOAD * FROM Table1\*.qvd (qvd);

Table2:

LOAD * FROM Table1\*.qvd (qvd);

JOIN (Table1) LOAD * RESIDENT Table2;

DROP TABLE Table2;

-Rob

http://masterssummit.com

http://robwunderlich.com

Re: Add data to qvd instead of replace it

Fri, 13 Jun 2014 17:19:35 GMT

We get an SAP table each night with the prior 7 days of data (LIKP_DELTA.QVD) and append the data to the main SAP(LIKP.QVD) table with:

LIKP:

load *,

VBELN As Key

From Q:\LIKP_DELTA.QVD (QVD);

Concatenate

load * ,

'-' as Key

From Q:\LIKP_USA.QVD (QVD) where NOT EXISTS(Key,VBELN);

drop field Key;

store * from LIKP into Q:\LIKP.QVD;

DROP TABLE LIKP;

Re: Add data to qvd instead of replace it

Fri, 13 Jun 2014 17:35:55 GMT

Thanks so much.