Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Orsikus
Contributor
Contributor

SAP EKPO Incremental Load Script

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

0 Replies