5 Replies Latest reply: Feb 9, 2018 1:47 AM by Baarathi V RSS

    Increamental load

    Baarathi V

      LastUpdatedDate:

      Load max(Date("Date of Joining")) as MaxDate

        FROM  lib://QVDs/abc.qvd(qvd);

      Let varThisExec = Date(Now());

      Let varLastExec = peek('MaxDate', 0, 'LastUpdatedDate');

      M:

      LOAD

          "First Name",

          "Last Name",

          "Date of Joining",

          Designation,

          Location,

          Salary,

          ID

      FROM [lib://QVDs/Employee.xls]

      (biff, embedded labels, table is Sheet1$)

      Where "Date of Joining" >= '$(varLastExec)';

      Concatenate

      LOAD

          "First Name",

          "Last Name",

          "Date of Joining",

          Designation,

          Location,

          Salary,

          ID

      from lib://QVDs/abc.qvd(qvd)

      where not Exists(ID);

      if '$(ScriptErrorCount)' = 0 then

      Store M into [lib://QVDs/abc.qvd](qvd);

      Let varLastExec = '$(varThisExec)';

      endif

       

       

      I was Trying to implement increamental load with insert, update and delete. When I tried with the above script. It's loading the entire data. Instead of Incremental Load. How to correct the above?

        • Re: Increamental load
          Ali Hijazi

          can you debug and check what '$(varLastExec)' is evaluated to?

          I mean check the value of this variable


          but I think you need to fix the line:

          Let varLastExec = peek('MaxDate', 0, 'LastUpdatedDate');

          to

          Let varLastExec = date(peek('MaxDate', 0, 'LastUpdatedDate'));

          • Re: Increamental load
            Jonathan Dienst

            The Where clause needs a properly formatted date. Modify your script:


            LastUpdatedDate:

            Load Max("Date of Joining") as MaxDate

              FROM  lib://QVDs/abc.qvd(qvd);

            Let varThisExec = Date(Now());

            Let varLastExec = Date(peek('MaxDate', 0, 'LastUpdatedDate'));

              • Re: Increamental load
                Baarathi V

                Thanks a lot. Insert and Update happens perfectly, but the delete in increatemental load in a problem in above script, how to solve it?

                  • Re: Increamental load
                    Jonathan Dienst

                    The problem with delete incremental loads is that you often need to read much more information to determine if something is missing. If the deletes happen in a limited time frame (say up to 30 days ago), then you could use a created date based approach with a cut-off date. In pseudocode, that could look like this

                     

                         Let vCutoff = Date(Today() - 30, 'yyyy/MM/dd');

                        

                         Data:

                         SQL SELECT ID,

                              CreatedDate,

                              ...

                         From ...

                         Where CreatedDate >= '$(vCutoff)';

                     

                         Concatenate

                         LOAD ID,

                              CreatedDate,

                              ...

                         FROM  .....qvd (qvd)

                         Where CreatedDate < '$(vCutoff)';