13 Replies Latest reply: Dec 16, 2013 10:38 AM by Rob Wunderlich RSS

    qvw incremental load  !!!not qvd!!!

      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!!!
          christian juillard

          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!!!

              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!!!
              Gysbert Wassenaar

              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!!!
                Frank Siciliani

                Have you tried using the partial load functionality?

                  • Re: qvw incremental load  !!!not qvd!!!
                    Peter Cammaert

                    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!!!
                    Istvan Szucs

                    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!!!

                      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!!!
                        David Robles

                        Try a Binary load.