topic Re: qvw incremental load !!!not qvd!!! in QlikView

qvw incremental load !!!not qvd!!!

Fri, 13 Dec 2013 15:44:39 GMT

Hi all,,

I am using the incremental load funcionality when I am creating qvd's <-- that is fine and it works.

My question is:

Can I load as well just new records to an existing .qvw

Example:

I have a QVD file which is refrehed (incremental) on daily base. This is my source for my QVW.

QVD ( 1. with data until 12 December) --> incremental load out of database --> QVD (2. with data until 13 December)

This whole process is saving time and it works.

Now I want to refresh my QVW:

I have in my .qvw data until the 12thDecember 2013, now I want to refresh my .qvw with the data (out of QVD 2) with just the data of the 13th December. Which means I dont want to load again the whole QVD 2 file into my QVW. I just want to add those new entries out of the QVD to the QVW. Because every time I refresh my QVW with the whole QVD2 file it is increasing the time for refresh ( due the fact that from day to day my QVD2 is getting bigger and bigger) I am talking here about 6billion rows for a timeframe of 1 1/2 months).

Hope somebody can help me and that my question is somehow understandable.

Re: qvw incremental load !!!not qvd!!!

Fri, 13 Dec 2013 15:46:49 GMT

Hi Ivan,

LOAD * FROM QVD1;

LOAD FROM QVD2 WHERE NOT EXISTS (DateFieldName); // will load only dates which are not referenced in QVD 1 prviously loaded

best regards

Chris

Re: qvw incremental load !!!not qvd!!!

Gysbert_Wassenaar — Fri, 13 Dec 2013 15:50:49 GMT

Not as far as I know. Qvd's are the way to do incremental loading. If you have that much data to work with you may have to look again if there are fields or records you can do without. Or perhaps you can aggregate data and only store and incrementally load aggregated data. You probably have a timestamp field too that could be split into day and time fields and perhaps an hour field too. Such fields will have a far lower cardinality and so compress better resulting in less memory usage.

Re: qvw incremental load !!!not qvd!!!

Fri, 13 Dec 2013 15:56:06 GMT

Hi Christian,

First of all thank you for your reply.

But this is not what I have asked for. (at least I think so)

Because in this case I would anyway need to load the compelte QVD1 and then the new entries out of QVD2.

What I am looking for is to keep already existing entries in a qvw and to add here new records just out of QVD2.

So if I have 10 entries with the date of 12 th December already in a existing qvw I just want to add (for example 3 new entries with 13 December) to my qvw.

Which in the end would mean that my QVW1 has in the beginning 10 entires and I run the script and 3 new lines get loaded into the QVW and in the end my QVW will contain 13 lines.

Is this even possible?

Or do I completly missunderstood something.

Re: qvw incremental load !!!not qvd!!!

Fri, 13 Dec 2013 15:59:12 GMT

If you could take a look to my reply to Christian would be much appreciated.

Thank you.

Re: qvw incremental load !!!not qvd!!!

sicilianif — Fri, 13 Dec 2013 18:06:09 GMT

Have you tried using the partial load functionality?

Re: qvw incremental load !!!not qvd!!!

Anonymous — Fri, 13 Dec 2013 18:18:39 GMT

Ivan

What you describe in your post of Dec 13, 2013 3:56 PM is not possible.

But do make sure for any QVD load ~~you do that is an Optimised load, they can be in the order of 100 times faster.~~

With those kinds of data volumes you may wish to investigate exploiting something like Google Big Query and use QV Direct Discovery. Such a task would be significant in time, effort & money.

Best Regards, Bill

Re: qvw incremental load !!!not qvd!!!

Sat, 14 Dec 2013 16:40:33 GMT

Ivan,

Every time you run the script in your QVW, you essentially delete the existing data model (both in terms of the schema and the actual data contained) and it is recreated based on the script that has run. It is not possible to do what you describe. With that said, QVDs are an extremely efficient way of loading into your QVWs, especially if you can make the load from the QVDs optimised.

When dealing with large volumes of data, you may also benefit from "partitioning" your data files. Basically, you can make your script pump out files like MyDataFile20131213.QVD, MyDataFile20131214.QVD, etc.
What I tend to do is generating two versions of the typically daily or monthly files: one with most granular data and one with aggregates. When you have these two versions of the same data, you can easily load the granular version into your QVW for most recent data and the aggregate version for older data. This would be done using a loop in your script. "Partitioning" the data file also helps with incremental load into the QVDs as you don't need to always load and append to the entire dataset but only to a smaller set of data.

Re: qvw incremental load !!!not qvd!!!

rwunderlich — Sat, 14 Dec 2013 18:18:05 GMT

This is possible.

1. Binary load the existing qvw.

2. Load additional data from qvd2.

-Rob

http://masterssummit.com

Re: qvw incremental load !!!not qvd!!!

Mon, 16 Dec 2013 08:24:17 GMT

OK IVan

SO let's say your qvw is Original.qvw

Copy it as New.qvw (you get all the front end)

Delete the script

Load bianry original.qvw

and LOAD * from QVD2 where not exists (Date)

Best regards

Chris

Re: qvw incremental load !!!not qvd!!!

Peter_Cammaert — Mon, 16 Dec 2013 10:05:35 GMT

I don't understand why everyone thinks this is not possible in QlikView.

As Frank says, use a QlikView Reload mode called "Partial Reload". In this mode, LOAD statements without ADD/REPLACE prefix will not be executed. LOAD statements with an ADD prefix will NOT delete the internal table, but will add to it according to the clauses you supply.

Not too tricky.

However, since you will still be reading/filtering an ever-growing QVD, performance will not necessarily improve...

Peter

Edit: corrected silly mistake

Re: qvw incremental load !!!not qvd!!!

Mon, 16 Dec 2013 10:18:50 GMT

Okay it seems like we have here couple of suggestions:

I will try out the thing with:

As Frank says, use a QlikView Reload mode called "Partial Reload". In this mode, LOAD statements without ADD/REPLACE prefix will start anew. LOAD statements with an ADD prefix will NOT delete the internal table, but will add to it according to the clauses you supply.

Thanks to everyone, its never easy and its never imposssible....

Re: qvw incremental load !!!not qvd!!!

Mon, 16 Dec 2013 15:14:56 GMT

Hi Ivan, You can Implement QVW incremental loading with "Partial Reload" but you have some limitations:

1. You can load only new data.

2. You cann'y handle updated or deleted data in this

Re: qvw incremental load !!!not qvd!!!

davidrobles — Mon, 16 Dec 2013 15:20:18 GMT

Try a Binary load.