5 Replies Latest reply: Feb 20, 2012 8:25 AM by Ashok Chandran RSS

    Issue of Incremental Load

      Brothers

       

      I am using ms sql server as my data source and looking forward to perform incremental reload on it

       

      let LastExecTime=?;

      let ModificationTime=?;

      QV_Table:

      LOAD customer,

          customername,

          city,

          state;

      SQL SELECT *

      FROM demo.dbo.customers

      Where $(ModificationTime) >= $(LastExecTime);

       

       

      what should be defination of above varables.

       

       

      Please let me have an example file on this topic

        • Re: Issue of Incremental Load

          Hope it will help to understand the incremental reload concept

          Refer the files attached.

           

           

           

          OLEDB CONNECT32 TO [Provider=SQLNCLI10.1;Integrated Security=SSPI;Persist Security Info=False;User ID="";Initial Catalog="";Data Source=IRFANGHORI-PC;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=IRFANGHORI-PC;Initial File Name="";Use Encryption for Data=False;Tag with column collation when possible=False;MARS Connection=False;DataTypeCompatibility=0;Trust Server Certificate=False];

           

          //Orders:

          //LOAD orderID,

          //    customer,

          //    orderDate,

          //    ShippingCost;

          //SQL SELECT *

          //FROM demo.dbo.orders;

          //

          //STORE Orders into Orders_qvd.qvd(qvd);

          //DROP Table Orders;

           

          set DeletedFlag=0;

          let LastExecTime=today();

          let ModificationTime=today()-1;

          let BeginningThisExecTime=today()+1;

           

          ListOfDeletedEntries:

          LOAD orderID as Deleted;

          //    customer,

          //    orderDate,

          //    ShippingCost;

          SQL SELECT orderID

          FROM demo.dbo.orders

          Where $(DeletedFlag) = 1;

           

           

           

           

          Orders:

          LOAD orderID,

              customer,

              orderDate,

              ShippingCost;

          SQL SELECT *

          FROM demo.dbo.orders

          Where $(ModificationTime) >=$(LastExecTime)

          and $(ModificationTime) <= $(BeginningThisExecTime);

           

          Concatenate

          LOAD orderID,

               customer,

               orderDate,

               ShippingCost

          FROM

          Orders_qvd.qvd

          (qvd)

          where not Exists(orderID)

          and

          not Exists(deleted,orderID);

           

          STORE Orders into Orders_qvd.qvd(qvd);

          • Issue of Incremental Load
            Ashok Chandran

            Hi

             

            ModificationTime  --> is refering the database last modified time (when the user insert/update/deledte any data its created one log file using the stored procedure or triggers)

             

             

            LastExecTime --> its referring the last reloaded time of your qlikview dashboard. here you can set the last reloaded time as like below

             

            Let LastExecTime = Date(ReloadTime(),'YYYY-MM-DD');  (here i mention the date format which is match with sql                                                                                                     server date format)

             

            Then you script like below,

             

            Let LastExecTime = Date(ReloadTime(),'YYYY-MM-DD');

             

            QV_Table:

            LOAD customer,

                customername,

                city,

                state;

            SQL SELECT *

            FROM demo.dbo.customers

            Where $(databaseTableDateField) >= $(LastExecTime);

             

             

            Regards

            Ashok

              • Re: Issue of Incremental Load

                Hi chandashok,

                 

                Hope you are fine, i am looking farword for your assistance,

                 

                The following query provide me the last updated date and time of the specified table

                 

                SELECT top 1

                last_user_update

                FROM sys.dm_db_index_usage_stats

                where OBJECT_NAME(OBJECT_ID)= 'orders';

                 

                 

                But the question i have is how to use this date as ModificationTime in my file.

                  • Re: Issue of Incremental Load
                    Ashok Chandran

                    Hi

                     

                         No need to take the last update date from the database.

                     

                         From qlikview itself you can compare the date and take the data from the database

                     

                     

                    Let LastExecTime = Date(ReloadTime(),'YYYY-MM-DD');

                        

                    SELECT *

                    FROM sys.dm_db_index_usage_stats

                    where OBJECT_NAME(OBJECT_ID)= 'orders'

                    And

                    your_database_table_dateField  >= LastExecTime ;

                     

                    Try this

                     

                    Regards

                     

                    Ashok

                • Issue of Incremental Load
                  Rob Wunderlich

                  You can use qlikview components http://qlikviewcomponents.org to simplify incremental load. See also

                  http://qlikviewnotes.blogspot.com/2012/01/incremental-load-using-qlikview.html

                   

                  Rob