2 Replies Latest reply: Jul 5, 2011 1:18 PM by Miguel Angel Baeyens de Arce RSS

    Incremental loading problem

      hi,

      i"m trying to load from DB all the rows that were updated using a "LAST_UPDATE_TIME" field in the DB.

      All the other rows I get from QVD.

       

      i"m using the following function:

       

      SUB concatenateAndStoreField (table_name,field_name)

      IF $(v_load_all_from_db) = 0

      THEN concatenate ($(table_name) )

      LOAD * FROM $(table_name).QVD (QVD)

      WHERE NOT Exists ($(field_name)) ;

      END IF

      Store $(table_name) into $(table_name).QVD;

       

      The problem is that if the field_name is not a key in the query(it means that it can appear more than once),

      The “NOT Exist” damage the QVD and removes all the rows except the first one.

       

      Is there any idea how to use the incremental loading differently ?

       

      Thanks,

      Avishay.

       

        • Incremental loading problem
          Stefan Wühl

          Hi,

           

          I think you should use "where not exists" only on fields with unique values.

           

          If field_name is not such a field, you can generate one (for example by combination of fields or artificial row number).

           

          There is a good example of incremental loading in the QlikView cookbook (http://robwunderlich.com/downloads/)

           

          Regards,

          Stefan

            • Re: Incremental loading problem
              Miguel Angel Baeyens de Arce

              Hello Avishay,

               

              I agree with Stefan in both using the EXISTS() with unique value fields and using the QlikView Cookbook.

               

              Taking a look at your script, there are several things I can think of

               

              First is that you might not need a loop to load from several tables, you can do a

               

              TABLE:
              LOAD *
              FROM *.QVD (QVD);
              

               

              Second is that you can load all values you want to keep (or get rid of) in a table and loop that table (or use a mapping table, or the exists clause) in the WHERE part to load only those records. The problem here is how do you identify uniquely those records you want to load. There are function such as Max() or FirstSortedValue() that usually return one value depending on the GROUP BY part, but if the values are not unique, you need something else.

               

              Post some sample data to see how can we help further.

               

              Regards.

               

              Miguel Angel Baeyens

              BI Consultant

              Comex Grupo Ibérica