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
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