Data append and update script

abhaysingh — Wed, 25 Nov 2020 16:16:04 GMT

Hi guys,

i have huge data for SAP cluster table BSEG which does not contain date in it only year i have , so i m struggling with incremental load in it.

so i want to split my data on yearly basis,, let say once i will extarct the data of 2015 ans store it into qvd..and now on daily basis i will extract the data of 2016 and use to append the 2016 data into 2015 data qvd...

but i m facing the problem like when i am appending 2016 data into 2015 data on daily basis my records are getting increasing...

i am using the script like below

Tab1:

load

PK,

F1,

from path...

Tab2:

load

PK,

F1,

from path where not exist(PK)..

Pls correct me in this if i m wrong some where ....

thanks in advance

Re: Data append and update script

amit_saini — Tue, 19 Jul 2016 05:15:30 GMT

Hi Abhay,

Sometimes it's very difficult to get millions/billions of records out of SAP , specially when you are dealing with big tables like MSEG , BSEG.

Below is my solution when you dealing with such tables:

Trick: First you will generate separate year wise QVD's and concatenate all generated yearly qvds into final one.

Script Solution:

// Delta or Initial Load

if not IsNull(QvdCreateTime('$(vQVD)\MKPF.qvd')) then

// delta load: read from existing qvd header
SET vLoadType_M = 'delta';

// 1. Get AEDAT and CPUDT
GetDate:
LOAD AEDAT, CPUDT
FROM $(vQVD)\MKPF.qvd (qvd);

// 2. Concatenate both dates in one column (e.g. to prevent null values)
ConDate:
LOAD AEDAT as DAT
Resident GetDate;

LOAD CPUDT as DAT
Resident GetDate;

// 3. Get the last date
MaxDate:
LOAD max(DAT) as MaxDAT
Resident ConDate
Group by 1;

LET vLastLoad_M = Date(Peek('MaxDAT', 0, 'MaxDate'), 'YYYYMMDD');
TRACE DELTALOAD for MKPF & MSEG, starting at $(vLastLoad_M);

DROP Tables GetDate, ConDate, MaxDate;

ELSE
// initial load

SET vLoadType_M = 'initial';
SET vLastLoad_M = '20120101'; // to be defined---------------------------------------------

Let vStartYear = Num(Year(Date#('$(vLastLoad_M)','YYYYMMDD')));      // Set Min Start Year
LET vEndYear = num(Year(Today()));             // Set Max Start Year

YearString:
LOAD Concat(Year_,chr(39) & ',' & Chr(39)) as LoopYear;        // Generate String for For each Loop if using initial Load
Load
'$(vStartYear)' + RecNo()-1   as Year_
AutoGenerate(vEndYear - vStartYear +1);

Let vYearLoopInitial = chr(39) & Peek('LoopYear',-1,'YearString') & chr(39); // Pick String for For each Loop if using initial Load
DROP Table YearString;

TRACE INITIALLOAD for MKPF & MSEG, starting at $(vLastLoad_M) for Years $(vYearLoopInitial);

ENDIF

if vLoadType_M = 'delta' then

MKPF:
LOAD
*,
MBLNR &'_'& MJAHR as MKPF_Key;
SQL SELECT
*
FROM MKPF
WHERE AEDAT GE '$(vLastLoad_M)' or CPUDT GE '$(vLastLoad_M)'
;

// add records from previous load:
concatenate(MKPF)
load * from MKPF.qvd(qvd)
// but exclude updated records:
where not exists (MKPF_Key);

STORE MKPF into MKPF.qvd;
DROP Table MKPF;

else

FOR Each vLoopYear in $(vYearLoopInitial) // Split MKPF in Years only for reduce amaount of Datasets during initial load

MKPF_$(vLoopYear):
LOAD
*,
MBLNR &'_'& MJAHR as MKPF_Key;
SQL SELECT
*
FROM MKPF where MJAHR = $(vLoopYear);

STORE MKPF_$(vLoopYear) into MKPF_$(vLoopYear).qvd;
DROP Table MKPF_$(vLoopYear);

NEXT

MKPF:
Load * from MKPF_*.qvd (qvd); // Concatenate all generated yearly MKPF Files to one
STORE MKPF into MKPF.qvd (qvd);
DROP Table MKPF;

endif

IF vLoadType_M = 'delta' THEN

MSEG:
LOAD
*,
MBLNR &'_'& ZEILE as MSEG_Key;
SQL SUBSELECT
* // please reduce to required fields, beside BUKRS, BELNR, GJAHR--------------------------------
FROM MSEG
WHERE MBLNR MJAHR IN (
SELECT MBLNR MJAHR FROM MKPF WHERE AEDAT GE '$(vLastLoad_M)' or CPUDT GE '$(vLastLoad_M)'
);

// add records from previous load:
concatenate(MSEG)
load * from MSEG.qvd(qvd)
// but exclude updated records:
where not exists (MSEG_Key);

STORE MSEG into MSEG.qvd;
DROP Table MSEG;

ELSE

FOR Each vLoopYear in $(vYearLoopInitial) // Split MSEG in Years only for reduce amaount of Datasets during initial load

MSEG_$(vLoopYear):
LOAD
*,
MBLNR &'_'& ZEILE as MSEG_Key;
SQL
* // please reduce to required fields, beside BUKRS, BELNR, GJAHR--------------------------------
FROM MSEG where MJAHR = $(vLoopYear);

STORE MSEG_$(vLoopYear) into MSEG_$(vLoopYear).qvd;
DROP Table MSEG_$(vLoopYear);

NEXT

MSEG:
Load * from MSEG_*.qvd (qvd); // Concatenate all generated yearly MSEG Files to one
STORE MSEG into MSEG.qvd (qvd);
DROP Table MSEG;

ENDIF

Hope this will help you!

Re: Data append and update script

abhaysingh — Tue, 19 Jul 2016 05:23:22 GMT

hi,

do u have qvw as well,, will be grt help to understand from it.. pls share if u can.. thanks..

Re: Data append and update script

amit_saini — Tue, 19 Jul 2016 05:41:47 GMT

PFA

Thanks,
AS

topic Re: Data append and update script in QlikView

Data append and update script

Re: Data append and update script

Re: Data append and update script

Re: Data append and update script