1 Reply Latest reply: Nov 5, 2012 10:24 AM by John Lifter RSS

    How to Fetch Incremental Data via SQL Query Read Operator

      Hi Community,

       

        I am working on SQL Query Read Operator  which read 4 records(Contains data of Purchase_ID , Creation_Date and Product) and hold them into .qvx file.

       

       

      The DataFlow1 is as follows.

       

      Capture.JPG

      The Query for DataFlow1 is as follows:

       

      select * from Purchase;

       

          

               I am looking forward to hold max value of Creation_Date, So that I can use it in next DataFlow which design to fetch only Incremental Data.

       

      The DataFlow2 is as follows:

      Capture.JPG

      The Query for DataFlow2 is as follows:

       

      select * from Purchase

      where Creation_Date>'Max value of Creation_Date';

       

       

      I mean if the 5th and 6th record are inserted into purchase table then 2nd .qvx file only contains them and not the previous 4 records. and overwrite the

      1st one with 6 records.

       

      can it be posible with expressor.

        • Re: How to Fetch Incremental Data via SQL Query Read Operator

          Currently it is not possible to do the type of processing you want in a SQL Query or Read Table operator.  But it is possible to use the Read Custom operator and the dssql module to execute this type of query.

           

          In your first dataflow, you need to insert either a Transform or Aggregate operator between the SQL Query and Write QlikView operators.  This operator will monitor the date value, and when all records have been processed it will use one of the utility.store_ functions to write that value as a persistent value.  For your objective, using a Transform operator is probably the best option as each record will be passed through to the output operator and the coding you add to monitor the date will be completely independent of the data pass-through.  An Aggregate operator will require that you provide a way for each record to be passed to the Write QlikView operator, which would involve more extensive coding.

           

          In your second dataflow, replace the SQL Query operator with the Read Custom operator.  Then create your own SELECT statement.  You use a utility.retrieve_ function to obtain the value of the last stored date and can incorporate that value into your WHERE clause.  The dataflow would also need to overwrite the stored date value so that the next time you run dataflow 2, you will only select records added since the last run.