6 Replies Latest reply: Jan 3, 2014 12:36 AM by Senthil Raja Branched to a new discussion. RSS

    applying incremental load from qlikview expressor

    Deepak Tibhe

      Hello Expert,

       

      Can you please tell me how to apply incremental load from qlikview expressor.

       

       

      Thanks,

      Deepak

        • Re: applying incremental load from qlikview expressor

          I Think This will be helps u go through this

           

          EMP_Unsort:

          load * from D:\EMP_inc.QVD(qvd);

           

           

           

           

          EMP_Sort:

          load * Resident EMP_Unsort order by HireDate;

           

           

          let MaxDate= peek('HireDate',-1,'EMP_Sort');

           

           

          set MaxDate1="'$(MaxDate)'";

           

           

          drop table EMP_Unsort;

           

           

           

           

           

           

           

           

           

           

          EMP_Load:

          SQL

           

           

          select * from MSBI.DBO.Employee

          where HireDate>$(MaxDate1);

           

           

          load * from D:\EMP_inc.QVD(qvd)

          where not Exists(employee_id);

           

           

          inner join (EMP_Load)

          select * from MSBI.DBO.Employee;

           

           

          store EMP_Load into D:\EMP_inc.QVD(qvd)

          • Re: applying incremental load from qlikview expressor
            Juan Gerardo Cabeza Luque

            Hi Deepak,

                 It will depend on your data sources and how you can extract incremental records. Here you have a very simple example with a typical situation:

             

            Capture.PNG.png

              • Re: applying incremental load from qlikview expressor
                Diamantis Archontoglou

                Theoretically this is how it works but in Qliivew and in other systems you have the option to create somehow

                a parametrized query and/or automatically determine based on a timestamp field the changed records (Delta).

                This is not available in QVE as we speak however...

                  • Re: Re: applying incremental load from qlikview expressor

                    An incremental load use case may be implemented with Expressor but exactly how you would set up such a dataflow depends on exactly what your use case requires (for example: insert only; insert and update; insert, update and delete) and how your source data identifies deleted records (generally by using a 'current' or 'deprecated' column to indicate whether a record is still valid).

                     

                    It is true that the Read Table operator does not have a property that would allow you to define a WHERE clause, and the SQL Query operator, which supports a WHERE clause, does not have a property that would allow runtime changes to the WHERE clause, but these behaviors do not prevent you from developing the incremental load use case.

                     

                    The most easily implemented approach is to include in the same database as your source data another table that stores the date and time of the most recent incremental load.  Then using the SQL Query operator your SELECT statement can include a WHERE clause that retrieves this value through a nested SELECT statement.  Something like:  SELECT * FROM ... WHERE mostRecentIncrementaloadDate > (SELECT mostRecentIncrementalLoadDate FROM ...).

                     

                     

                    Another approach is to use the Read Custom operator and DSSQL datascript module that is included with Expressor.  With this approach you can store the date and time of the last incremental load as an Expressor persistent value and your code can retrieve this value and incorporate it into the SELECT statement run against your data source.

                     

                    Once the SELECT statement has extracted new, updated, and deleted records from the source data, the dataflow simply uses two Join operators to differentiate between the new, updated, and deleted records, as shown in the following figure.incremental_load.png

                    • The Filter operator uses the value of the 'current' or 'deprecated' column to separate new and updated records from deleted records.
                    • The New & Updated Join operator performs an inner join between the existing records and the new and updated records.  Updated records engage in a successful join and are emitted from the output port on the right side of the operator.  New records are emitted from the right port on the bottom edge of the operator.  All other records are emitted from the left port on the bottom edge of the operator and are subjected to an inner join with the deleted records in the Deleted Employees Join operator.
                    • Those records that engage in a successful join represent deleted records, are emitted from the output port on the operator's right side, and are discarded.
                    • Records emitted from the left port on the bottom edge of the operator represent records that were neither deleted nor updated.
                    • The new, updated, and untouched records are then combined by the Funnel operator and written to a new output file.

                     

                    Note that the Read Salesforce operator does include a Filter property that would allow you to use a parameter or configuration value to change at runtime the records retrieved from the data source.