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;
