0 Replies Latest reply: Jun 22, 2018 11:21 AM by Achraf Baiz RSS

    problem with incremental load

    Achraf Baiz

      I put in place this simple incremental load script, it works fine as it's correct, but when i run the load script each time so that i can fetch new/modified records from my data source i end up with wrong data, some become null and the number of rows is not correct.

       

      please tell me what is wrong with the script. thanks in advance.

       

      script is here  :

       

      Let exist = isnull(QvdCreateTime('lib://alm_prod_db/test_.qvd'));

      If exist=-1 then

      [TEST]:

      LOAD

        [TS_TEST_ID] AS [RN_TEST_ID_TS_TEST_ID],

        [TS_NAME],

        [TS_STEPS],

        [TS_STATUS],

        [TS_EXEC_STATUS],

        [TS_TYPE],

        [TS_CREATION_DATE],

        Day([TS_CREATION_DATE]) as [TS_CREATION_DATE_DAY],

        Num(Month([TS_CREATION_DATE])) as [TS_CREATION_DATE_MONTH],

        Year([TS_CREATION_DATE]) as [TS_CREATION_DATE_YEAR];

       

      SQL SELECT "TS_TEST_ID",

        "TS_NAME",

        "TS_STEPS",

        "TS_STATUS",

        "TS_EXEC_STATUS",

        "TS_TYPE",

        "TS_CREATION_DATE"

      FROM "database"."td"."TEST";

      Store [TEST] into [lib://alm_prod_db/test_.qvd](qvd);

      else

      /////

      [FetchMaxDate]:

      Load Date(Max([TS_CREATION_DATE])) as maxdate FROM [lib://alm_prod_db/test_.qvd](qvd);

      LET LastDateInDH = Peek('maxdate');

      DROP Table FetchMaxDate;

      /////

      [TEST_INCREMENTAL]:

      LOAD

        [TS_TEST_ID] AS [RN_TEST_ID_TS_TEST_ID],

        [TS_NAME],

        [TS_STEPS],

        [TS_STATUS],

        [TS_EXEC_STATUS],

        [TS_TYPE],

        [TS_CREATION_DATE],

        Day([TS_CREATION_DATE]) as [TS_CREATION_DATE_DAY],

        Num(Month([TS_CREATION_DATE])) as [TS_CREATION_DATE_MONTH],

        Year([TS_CREATION_DATE]) as [TS_CREATION_DATE_YEAR];

      SQL SELECT "TS_TEST_ID",

        "TS_NAME",

        "TS_STEPS",

        "TS_STATUS",

        "TS_EXEC_STATUS",

        "TS_TYPE",

        "TS_CREATION_DATE"

      FROM "database"."td"."TEST"

      where "TS_CREATION_DATE" >= '$(LastDateInDH)';

      Concatenate LOAD *

      FROM [lib://alm_prod_db/test_.qvd](qvd)

      WHERE NOT Exists("RN_TEST_ID_TS_TEST_ID");

      Store [TEST_INCREMENTAL] into [lib://alm_prod_db/test_.qvd](qvd);

      End IF