Discussion Board for collaboration on QlikView Scripting.
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
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.
LOAD * FROM QVD1;
LOAD FROM QVD2 WHERE NOT EXISTS (DateFieldName); // will load only dates which are not referenced in QVD 1 prviously loaded
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.
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.
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
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.
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)