13 Replies Latest reply: Apr 26, 2016 5:41 AM by xavier hemelaar RSS

    Partial LOAD

    xavier hemelaar

      Hi Qlikers,

       

       

       

      I look at the different posts regarding partial Load with the function "Replace" or "Add". But in my case there is something I still can figure out.

       

      Let’s say my script is like this:

       

      DATA:

      LOAD

       

      *

       

      FROM ... JAN 2016;

       

       

      DATA:

      LOAD

       

      *

       

      FROM ... FEB 2016;

       

       

       

      DATA:

      REPLACE LOAD

       

      *

       

      FROM ... THIS MONTH;

       

       

      So every time it is loading all the files. But in fact I need only the data from the file “THIS MONTH” to be updated.

       

       

      Because I have one big table called DATA, if I use REPLACE before LOAD for the file THIS MONTH, the only thing left will be the data from this month. But I want to keep all the data and replacing only the one for this month.

       

      Is there a way to do so?

       

      Thanks for your help

        • Re: Partial LOAD
          Sunny Talwar

          I guess replace load replaces the existing table with a new table. Do you have a different table name for the table that contains this month's data? Can you may be post your actual script?

          • Re: Partial LOAD
            Jonathan Dienst

            I think what you need is an incremental load, rather than a partial reload. Your incremental load would load the prior months from monthly qvd files. Although you are loading all the data, the qvd loads can be very fast compared to the original data source.

             

            The load for the current month would continually update the qvd for the current month, so that when the month ends, it lands up as a history qvd. Something like:

             

            Let vCurrentMonthFile = Date(Today(), 'MMM yyyy') & '.qvd';

             

            DATA:     // replace with the correct load statement for the current period

            LOAD *

            FROM ......;

             

            // replace <your file path> with the correct file path - replaces the current month file every day

            STORE DATA into ['<your file path>$(vCurrentMonthFile)'];

             

            // now load the history - make sure to not load the current month

            For Each vFile in FileList('<your file path>\*.qvd')

              If SubField(vFile, '\', -1) <> vCurrentMonth Then

              DATA:

              LOAD * FROM [$(vFile)] (qvd);

              End If

            Next

             

            When the month ticks over, the previous month's qvd will contain the values for the month and you will start on a new qvd.

            • Re: Partial LOAD
              xavier hemelaar

              Hi,

               

              I was already creating several QVD for each month. But still it takes a lot of time to load them all. So I found a way…

               

              1st:

               

              REPLACE ONLY LOAD

              *

              RESIDENT DATA

              Where ImportDate<date(today(1));

               

              2nd

              LOAD

              *

              FROM THISMONTH.QVD

              Where ImportDate>date(today(1));

               

              Loading time is divided by 6:

               

              Thanks for your help

                • Re: Partial LOAD
                  Sunny Talwar

                  I think 1st is a table which doesn't exist in your application before the partial reload?

                   

                  If that is true, you would need to do Add Only instead of Replace Only. But for every subsequent partial reload 1st will continue to exists. So to drop it each time, you can use an if statement:

                   

                  If NoOfRow('1st') > 0 then

                      

                       DROP Table 1st

                   

                  ENDIF

                   

                  1st:

                  Add ONLY LOAD

                  *

                  RESIDENT DATA

                  Where ImportDate<date(today(1));

                • Re: Partial LOAD
                  xavier hemelaar

                  Hi,

                  I think I made a mistake. I thought that it was possible to use RESIDENT DATA when I relaod my app. But it tels me that table is not found.

                  Is there a way to Call a table that already exist in my App?

                  Thanks for your help.