2 Replies Latest reply: Sep 7, 2015 11:24 PM by Sara Khalili RSS

    Incremental load via max id

      Hi every body,

       

      I have created the report which uses incremental load with below script but unfortunately there is duplicate record. would you please help me?

       

       

      let vexistsqvd=isnull(QvdCreateTime('IncrementalloadCardex.qvd'));

       

       

       

      if vexistsqvd=-1 then

          [incremental load Cardex]:

       

       

      LOAD fldPJuncCardTexAutoID,

          fldPJuncCardTexID,

          fldPJuncCardTexProductID As ProductID,

          fldPJuncCardTexDate As date,

          fldPJuncCardTexHDate,

          fldPJuncCardTexOpeningBalance,

          fldPJuncCardTexInput,

          fldPJuncCardTexOutput,

          fldPJuncCardTexProduction,

          fldPJuncCardTexSales,

          fldPJuncCardTexSalesFree,

          fldPJuncCardTexScrapt,

          fldPJuncCardTexLeak,

          fldPJuncCardTexReject,

          fldPJuncCardTexIncDec,

          fldPJuncCardTexIncomingProduction,

          fldPJuncCardTexClosingBalance,

          fldPJuncCardTexMTDProduction,

          fldPJuncCardTexYTDProduction,

          fldPJuncCardTexMTDSales,

          fldPJuncCardTexYTDSales,

          fldPJuncCardTexMTDScrapt,

          fldPJuncCardTexYTDScrapt,

          fldPJuncCardTexMTDLeak,

          fldPJuncCardTexYTDLeak,

          fldPJuncCardTexMTDReject,

          fldPJuncCardTexYTDReject,

          fldPJuncCardTexForecastDayProduction,

          fldPJuncCardTexForecastDayPrimerySales,

          fldPJuncCardTexForecastDaySeconderySales,

          fldPJuncCardTexForecastDayProductionOrder,

          fldPJuncCardTexForecastDayProductionOriginal,

          fldPJuncCardTexForecastDayRollingRevision,

          fldPJuncCardTexForecastMonthProduction,

          fldPJuncCardTexForecastMonthPrimerySales,

          fldPJuncCardTexForecastMonthSeconderySales,

          fldPJuncCardTexForecastMonthProductionOrder,

          fldPJuncCardTexForecastMonthProductionOriginal,

          fldPJuncCardTexForecastMonthRollingRevision,

          fldPJuncCardTexForecastYearProduction,

          fldPJuncCardTexForecastYearPrimerySales,

          fldPJuncCardTexForecastYearSeconderySales,

          fldPJuncCardTexForecastYearProductionOrder,

          fldPJuncCardTexForecastYearProductionOriginal,

          fldPJuncCardTexForecastYearRollingRevision,

          fldPJuncCardTexSecondaryStock,

          fldPJuncCardTexSecondarySales,

          fldPJuncCardTexSecondarySalesMTD,

          fldPJuncCardTexSecondarySalesYTD,

          fldPJuncCardTexLMTDSales,

          fldPJuncCardTexLYTDSales,

          fldPJuncCardTexLMTDReject,

          fldPJuncCardTexLYTDReject,

          fldPJuncCardTexFullMonthPlan,

          fldPJuncCardTexFullMonthForecast,

          fldPJuncCardTexFullMonthRollingRevision,

          fldPJuncCardTexFullMonthProductionOrder,

          fldPJuncCardTexFullYearPlan,

          fldPJuncCardTexFullYearForecast,

          fldPJuncCardTexFullYearRollingRevision,

          fldPJuncCardTexFullYearProductionOrder,

          fldPJuncCardTexSecondarySalesLMTD,

          fldPJuncCardTexSecondarySalesLYTD,

          fldPJuncCardTexFullNextMonthPlan,

          fldPJuncCardTexFullNextMonthForecast,

          fldPJuncCardTexFullNextMonthRollingRevision,

          fldPJuncCardTexFullNextMonthProductionOrder,

          fldPJuncCardTexFullNext2MonthPlan,

          fldPJuncCardTexFullNext2MonthForecast,

          fldPJuncCardTexFullNext2MonthRollingRevision,

          fldPJuncCardTexFullNext2MonthProductionOrder,

          fldPJuncCardTexHDateYM;

      SQL SELECT *

      FROM RMWH.dbo.tblPJuncCardTex;

      Store [incremental load Cardex] into IncrementalloadCardex.qvd(qvd);

      Drop Table [incremental load Cardex];

      end if;

       

       

      if vexistsqvd=0 then

      ListOFCardex:

      LOAD fldPJuncCardTexAutoID,

          fldPJuncCardTexID,

          ProductID,

          date,

          fldPJuncCardTexHDate,

          fldPJuncCardTexOpeningBalance,

          fldPJuncCardTexInput,

          fldPJuncCardTexOutput,

          fldPJuncCardTexProduction,

          fldPJuncCardTexSales,

          fldPJuncCardTexSalesFree,

          fldPJuncCardTexScrapt,

          fldPJuncCardTexLeak,

          fldPJuncCardTexReject,

          fldPJuncCardTexIncDec,

          fldPJuncCardTexIncomingProduction,

          fldPJuncCardTexClosingBalance,

          fldPJuncCardTexMTDProduction,

          fldPJuncCardTexYTDProduction,

          fldPJuncCardTexMTDSales,

          fldPJuncCardTexYTDSales,

          fldPJuncCardTexMTDScrapt,

          fldPJuncCardTexYTDScrapt,

          fldPJuncCardTexMTDLeak,

          fldPJuncCardTexYTDLeak,

          fldPJuncCardTexMTDReject,

          fldPJuncCardTexYTDReject,

          fldPJuncCardTexForecastDayProduction,

          fldPJuncCardTexForecastDayPrimerySales,

          fldPJuncCardTexForecastDaySeconderySales,

          fldPJuncCardTexForecastDayProductionOrder,

          fldPJuncCardTexForecastDayProductionOriginal,

          fldPJuncCardTexForecastDayRollingRevision,

          fldPJuncCardTexForecastMonthProduction,

          fldPJuncCardTexForecastMonthPrimerySales,

          fldPJuncCardTexForecastMonthSeconderySales,

          fldPJuncCardTexForecastMonthProductionOrder,

          fldPJuncCardTexForecastMonthProductionOriginal,

          fldPJuncCardTexForecastMonthRollingRevision,

          fldPJuncCardTexForecastYearProduction,

          fldPJuncCardTexForecastYearPrimerySales,

          fldPJuncCardTexForecastYearSeconderySales,

          fldPJuncCardTexForecastYearProductionOrder,

          fldPJuncCardTexForecastYearProductionOriginal,

          fldPJuncCardTexForecastYearRollingRevision,

          fldPJuncCardTexSecondaryStock,

          fldPJuncCardTexSecondarySales,

          fldPJuncCardTexSecondarySalesMTD,

          fldPJuncCardTexSecondarySalesYTD,

          fldPJuncCardTexLMTDSales,

          fldPJuncCardTexLYTDSales,

          fldPJuncCardTexLMTDReject,

          fldPJuncCardTexLYTDReject,

          fldPJuncCardTexFullMonthPlan,

          fldPJuncCardTexFullMonthForecast,

          fldPJuncCardTexFullMonthRollingRevision,

          fldPJuncCardTexFullMonthProductionOrder,

          fldPJuncCardTexFullYearPlan,

          fldPJuncCardTexFullYearForecast,

          fldPJuncCardTexFullYearRollingRevision,

          fldPJuncCardTexFullYearProductionOrder,

          fldPJuncCardTexSecondarySalesLMTD,

          fldPJuncCardTexSecondarySalesLYTD,

          fldPJuncCardTexFullNextMonthPlan,

          fldPJuncCardTexFullNextMonthForecast,

          fldPJuncCardTexFullNextMonthRollingRevision,

          fldPJuncCardTexFullNextMonthProductionOrder,

          fldPJuncCardTexFullNext2MonthPlan,

          fldPJuncCardTexFullNext2MonthForecast,

          fldPJuncCardTexFullNext2MonthRollingRevision,

          fldPJuncCardTexFullNext2MonthProductionOrder,

          fldPJuncCardTexHDateYM

      from IncrementalloadCardex.qvd(qvd);

       

       

      MaxCardexIDFromQVd:

        Load max(fldPJuncCardTexAutoID)As maxid

        Resident ListOFCardex;

       

        let Vmaxid=peek('maxid',0,'MaxCardexIDFromQVd');

       

        Drop table MaxCardexIDFromQVd;

       

        Drop table ListOFCardex;

        NoConcatenate

        NewData:

       

       

        LOAD fldPJuncCardTexAutoID,

          fldPJuncCardTexID,

          fldPJuncCardTexProductID As ProductID,

          fldPJuncCardTexDate As date,

          fldPJuncCardTexHDate,

          fldPJuncCardTexOpeningBalance,

          fldPJuncCardTexInput,

          fldPJuncCardTexOutput,

          fldPJuncCardTexProduction,

          fldPJuncCardTexSales,

          fldPJuncCardTexSalesFree,

          fldPJuncCardTexScrapt,

          fldPJuncCardTexLeak,

          fldPJuncCardTexReject,

          fldPJuncCardTexIncDec,

          fldPJuncCardTexIncomingProduction,

          fldPJuncCardTexClosingBalance,

          fldPJuncCardTexMTDProduction,

          fldPJuncCardTexYTDProduction,

          fldPJuncCardTexMTDSales,

          fldPJuncCardTexYTDSales,

          fldPJuncCardTexMTDScrapt,

          fldPJuncCardTexYTDScrapt,

          fldPJuncCardTexMTDLeak,

          fldPJuncCardTexYTDLeak,

          fldPJuncCardTexMTDReject,

          fldPJuncCardTexYTDReject,

          fldPJuncCardTexForecastDayProduction,

          fldPJuncCardTexForecastDayPrimerySales,

          fldPJuncCardTexForecastDaySeconderySales,

          fldPJuncCardTexForecastDayProductionOrder,

          fldPJuncCardTexForecastDayProductionOriginal,

          fldPJuncCardTexForecastDayRollingRevision,

          fldPJuncCardTexForecastMonthProduction,

          fldPJuncCardTexForecastMonthPrimerySales,

          fldPJuncCardTexForecastMonthSeconderySales,

          fldPJuncCardTexForecastMonthProductionOrder,

          fldPJuncCardTexForecastMonthProductionOriginal,

          fldPJuncCardTexForecastMonthRollingRevision,

          fldPJuncCardTexForecastYearProduction,

          fldPJuncCardTexForecastYearPrimerySales,

          fldPJuncCardTexForecastYearSeconderySales,

          fldPJuncCardTexForecastYearProductionOrder,

          fldPJuncCardTexForecastYearProductionOriginal,

          fldPJuncCardTexForecastYearRollingRevision,

          fldPJuncCardTexSecondaryStock,

          fldPJuncCardTexSecondarySales,

          fldPJuncCardTexSecondarySalesMTD,

          fldPJuncCardTexSecondarySalesYTD,

          fldPJuncCardTexLMTDSales,

          fldPJuncCardTexLYTDSales,

          fldPJuncCardTexLMTDReject,

          fldPJuncCardTexLYTDReject,

          fldPJuncCardTexFullMonthPlan,

          fldPJuncCardTexFullMonthForecast,

          fldPJuncCardTexFullMonthRollingRevision,

          fldPJuncCardTexFullMonthProductionOrder,

          fldPJuncCardTexFullYearPlan,

          fldPJuncCardTexFullYearForecast,

          fldPJuncCardTexFullYearRollingRevision,

          fldPJuncCardTexFullYearProductionOrder,

          fldPJuncCardTexSecondarySalesLMTD,

          fldPJuncCardTexSecondarySalesLYTD,

          fldPJuncCardTexFullNextMonthPlan,

          fldPJuncCardTexFullNextMonthForecast,

          fldPJuncCardTexFullNextMonthRollingRevision,

          fldPJuncCardTexFullNextMonthProductionOrder,

          fldPJuncCardTexFullNext2MonthPlan,

          fldPJuncCardTexFullNext2MonthForecast,

          fldPJuncCardTexFullNext2MonthRollingRevision,

          fldPJuncCardTexFullNext2MonthProductionOrder,

          fldPJuncCardTexHDateYM;

      SQL SELECT *

          FROM RMWH.dbo.tblPJuncCardTex

          where fldPJuncCardTexAutoID > $(Vmaxid);

          Concatenate

        load * from IncrementalloadCardex.qvd(qvd) where not Exists (fldPJuncCardTexAutoID);

       

          Store NewData into IncrementalloadCardex.qvd(qvd);

       

       

       

            end if;