topic Regarding Incremental Load in QlikView

Regarding Incremental Load

Fri, 19 Jul 2013 05:16:33 GMT

It is always taking around 2 minutes time to execute the following code. In my opinion the time should be decresed during 2nd run. Please help.

IF IsNull(FileTime($(vSaveQVD)tTankMC.qvd)) then
tTankMC:

   LOAD TEST, BUDAT, ARBPL, WERKS;

   SQL SELECT

      concat(concat(concat(a.WERKS, a.grund),a.budat),a.aufnr) as test,

      a.budat,

      b.arbpl,

      a.werks

      FROM afru a,crhd b

      WHERE a.grund IN ( SELECT c.shift3 FROM zshift_tab c )

      AND a.vornr = '0010'

      AND a.stzhl = 0

      AND a.werks=b.werks

      AND a.arbid=b.objid

      AND a.aufnr not in (select d.aufnr from aufk d where d.auart = 'PRFD')

      AND b.arbpl like '%FH%';

   STORE tTankMC into $(vSaveQVD)tTankMC.qvd (qvd);

ELSE

   tTankMC:

   LOAD TEST, BUDAT, ARBPL, WERKS;

   SQL SELECT

      concat(concat(concat(a.WERKS, a.grund),a.budat),a.aufnr) as test,

      a.budat,

      b.arbpl,

      a.werks

      FROM afru a,crhd b

      WHERE a.grund IN ( SELECT c.shift3 FROM zshift_tab c )

      AND a.vornr = '0010'

      AND a.stzhl = 0

      AND a.werks=b.werks

      AND a.arbid=b.objid

      AND a.aufnr not in (select d.aufnr from aufk d where d.auart = 'PRFD')

      AND b.arbpl like '%FH%';

   Concatenate

   LOAD TEST, BUDAT, ARBPL, WERKS

   FROM [$(vSaveQVD)tTankMC.qvd] (qvd)

   Where not Exists(TEST);

   IF ScriptErrorCount = 0 then

      STORE tTankMC into $(vSaveQVD)tTankMC.qvd (qvd);

      LET LastExecTime = BeginningThisExecTime;

   ENDIF

ENDIF

Re: Regarding Incremental Load

Fri, 19 Jul 2013 05:28:52 GMT

Hello

Try to execute it through some variable..as because when reload starts 1st execute all variable,then script..this will help u for performances..pls follow below scripts..

LET vQvdexists=isnull(QvdCreateTime('Extract.qvd')); // if qvd exists then 0 else -1

If $(vQvdexists) = 0 then
set vLoadType = 'Delta';
Else
set vLoadType = 'Initial';
Endif

If vLoadType = 'Delta' then

LoadQVD:
Load *;
SQL......

....;
ELSE

LoadQVD:
Load *;
SQL.......

...;
End if

regards-bika

Re: Regarding Incremental Load

suniljain — Fri, 19 Jul 2013 05:32:35 GMT

total load time have dependencies on volume of your increamental data and historized data . can you tell us increamental data volume and historized data volume ?.

Re: Regarding Incremental Load

Mon, 22 Jul 2013 04:57:29 GMT

I am extremetly sorry Jainji. I could not reply in time.

I am unable to inform you about the data in afru table. But around 3 lakhs records are fetching.

I am giving the codes once again for your understanding. I have made some changes.

tTankMC:

LOAD WERKS, ARBPL, GRUND, AUFNR, ARBID, BUDAT, XMNGA, LMNGA;

   SQL SELECT

      a.werks,

      b.arbpl,

      a.grund,

      a.aufnr,

      a.arbid,

      a.budat,

      a.xmnga,

      a.lmnga

      FROM afru a,crhd b

      WHERE a.grund IN ( SELECT c.shift3 FROM zshift_tab c )

      AND a.vornr = '0010'

      AND a.stzhl = 0

      AND a.stokz <> 'X'

      AND a.werks = b.werks

      AND a.arbid = b.objid

      AND (a.lmnga <> 0 OR a.xmnga <> 0)

      AND a.laeda >= #$(lastExecTime)#

      AND a.laeda < #$(BeginningThisExecTime)#

      AND b.arbpl like '%FH%';

   Concatenate

   LOAD WERKS, ARBPL, GRUND, AUFNR, ARBID, BUDAT, XMNGA, LMNGA

   FROM [$(vSaveQVD)tTankMC.qvd] (qvd) WHERE NOT exists(WERKS, ARBPL, GRUND, AUFNR, ARBID, BUDAT);

   IF ScriptErrorCount = 0 then

      STORE tTankMC into $(vSaveQVD)tTankMC.qvd (qvd);

   ENDIF

Now, if I re-run the same no data should generate but still it's taking around 2 minutes time.