Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys! I'm in trouble about loading new and modified records from a SAP source table named 'EKPO'.
What I need is to upload all the new records created until last update of .qvd. It's possibile that some records are only updated (until last store of .qvd) so I need to overwrite them, append all new one and mantain all the old records. My scrips is in follow. Where I'm wrong?
In the attached file you will find the result I expect.
*************** script *************************
vLastExecTime = makeDate(1970,01,01);
if not isnull(QvdCreateTime('$(vStoreQVD)/1.Staging/St_TAB_EKPO.qvd')) then
if not isnull(QvdCreateTime('$(vStoreQVD)/1.Staging/loadtimes.qvd')) then
LoadTime:
Load Max(LastModifiedDate) AS LastModifiedDate
From [$(vStoreQVD)/1.Staging/loadtimes.qvd] (qvd)
Where TableName='St_TAB_EKPO';
if NoOfRows('LoadTime')>0 then
vLastExecTime = Peek('LastModifiedDate',0,'LoadTime');
end if
if IsNull( vLastExecTime ) then
vLastExecTime = MakeDate(1970,1,1);
end if
Drop Table LoadTime;
End If
end if
let vLastExecTimeStr = TimeStamp(vLastExecTime,'YYYYMMDD');
if isnull(QvdCreateTime('$(vStoreQVD)/1.Staging/St_TAB_EKPO.qvd')) then
[EKPONEW]:
LOAD
*,
EBELN &'_'& EBELP as EKPO_Key;
SELECT
*
FROM EKPO WHERE BUKRS <> 'FTH1';
else
[EKPONEW]:
LOAD
*,
EBELN &'_'& EBELP as EKPO_Key;
SELECT
*
FROM EKPO WHERE AEDAT>='$(vLastExecTimeStr)' and BUKRS <> 'FTH1';
end if
// *********************************************************************************************
if not isnull(QvdCreateTime('$(vStoreQVD)/1.Staging/St_TAB_EKPO.qvd')) then
Concatenate('EKPONEW')
Load
*
From [$(vStoreQVD)/1.Staging/St_TAB_EKPO.qvd] (qvd) where NOT EXISTS(EKPO_Key) ;
end If
// // *********************************************************************************************
if (NoOfRows('EKPONEW'))>0 then
STORE * FROM [EKPONEW] INTO [$(vStoreQVD)/1.Staging/St_TAB_EKPO.qvd] (qvd);
LET vLastExecTime = Now();
if not isnull(QvdCreateTime('$(vStoreQVD)/1.Staging/loadtimes.qvd')) then
LoadTime:
Load *from [$(vStoreQVD)/1.Staging/loadtimes.qvd] (qvd) where TableName<>'St_TAB_EKPO';
end if
LoadTime:
Load * Inline [
TableName,LastModifiedDate
St_TAB_EKPO,$(vLastExecTime)
];
Store LoadTime into [$(vStoreQVD)/1.Staging/loadtimes.qvd] (qvd);
Drop Table LoadTime;
End if
DROP TABLE [EKPONEW];