Incremental load via max id

Mon, 07 Sep 2015 10:01:25 GMT

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;

topic Incremental load via max id in QlikView

Incremental load via max id

Re: Incremental load via max id

Re: Incremental load via max id