1 Reply Latest reply: Sep 11, 2017 11:10 AM by Marcus Sommer RSS

    Incremental load - merging 2 tables

    Twan Peters

      I've a simple incremental load statement that i'm currently testing with  a sample data set of 526 records in a test table (CON_ACTIVITY).

      Every time i run the script the QVD grows in size, to be more specific, when i do a count on of the fields, it multiplies with every load. This is unexpected for me, because the test data set is not growing.

       

      I removed the where conditions in the script to make it easier understandable. Basically what i'm hoping to achieve:

      1. I load data from the CON_ACTIVITY table

      2. I load data from a QVD, same format.

      3. I place the data in 1 table, and Qlikview merges the duplicates. This works when i view the data in a table box, but the size of the QVD grows, and the count on the fields also increases with every load, hence, Qlikview is saving the duplicated records.

       

      I also tried a Concatenate function, but this also resulted in a growing table. What am i doing wrong here?

       


      TEMP_D_CAMPAIGN_ACTIVITY:

      SELECT

      ACTIVITY_TYPE_CODE_FK as ACTIVITY_TYPE,

      ACTIVITY_DATE_TIME as ACTIVITY_DATE,

      CONSUMER_FK_ID as CONSUMER_ID_FK 

      FROM $(TAP).CON_ACTIVITY;

       

      OUTER JOIN

       

      LOAD

      ACTIVITY_TYPE,

      ACTIVITY_DATE,

      CONSUMER_ID_FK

      FROM $(QVDpath)D_CAMPAIGN_ACTIVITY_1000_DAYS.qvd (qvd);

       

      NoConcatenate

       

      D_CAMPAIGN_ACTIVITY:

      LOAD

      ACTIVITY_TYPE,

      ACTIVITY_DATE,

      CONSUMER_ID_FK

      RESIDENT TEMP_D_CAMPAIGN_ACTIVITY;

       

      STORE D_CAMPAIGN_ACTIVITY into $(QVDpath)D_CAMPAIGN_ACTIVITY_1000_DAYS.qvd (qvd);

       

      DROP Table TEMP_D_CAMPAIGN_ACTIVITY;

      DROP Table D_CAMPAIGN_ACTIVITY;

        • Re: Incremental load - merging 2 tables
          Marcus Sommer

          The logic should be look more like:

           

          Table:

          select UniqueKey, Field1, Field2 ... from Source;

               concatenate(Table)

          load UniqueKey, Field1, Field2 ... from qvd where not exists(UniqueKey);

           

          A tablebox without adding an UniqueKey field to it is only partly suitable to check which data are available. Without a real UniqueKey in the data you could use recno() and/or rowno() within the table-load to make the records of the tablebox unique.

           

          - Marcus