topic Re: Incremental load in QlikView

Incremental load

deepakqlikview_123 — Thu, 20 Mar 2014 12:24:02 GMT

Please look at below code,

Can you please help me out how this will work

===========================================================================

Incremental load

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=PVPL_BI_USER;Initial Catalog=CDMSLIVE;Data Source=108.168.226.124;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=BISRV;Use Encryption for Data=False;Tag with column collation when possible=False] (XPassword is GDKCKIZKQCTEUEVKTKREVdA);

let QvdPath = 'E:\Piaggio\Distribution\Increamental\Data\';

let DailyQvd = 'E:\Piaggio\Distribution\Increamental\Data\Daily Data\';

let vDate =Timestamp(Now(1),'YYYY-MM-DD');
=============================================================

LET vDeltaBegins=Timestamp(Now(1),'YYYY-MM-DD hh:mm:ss');

DELTA_PSA:
LOAD
SERIES,
     LastTime,
     LoadType,
     LoadScript,
     TotalReloadTime

FROM $(QvdPath)\DELTA_PSA.QVD (Qvd);

DELTA_PSA1:
LOAD
MAX(SERIES) AS LastSeriesNo
RESIDENT DELTA_PSA;

DELTA_PSA2:
LOAD
Timestamp(MAX(LastTime),'YYYY-MM-DD hh:mm:ss') AS LastReloadTime
RESIDENT DELTA_PSA;
//where LoadScript='$(VPARAM)';

LET vLastSeriesNo=peek('LastSeriesNo',-1,'DELTA_PSA1');
LET vLastSeriesNo=$(vLastSeriesNo)+1;
LET vLastReloadTime=TIMESTAMP(PEEK('LastReloadTime',-1,'DELTA_PSA2'),'YYYY-MM-DD hh:mm:ss');
LET vLoadScript='$(VPARAM)';

DROP TABLE DELTA_PSA,DELTA_PSA1,DELTA_PSA2;
==================================================================
SET QVDFILE = $(QvdPath)VES_VehiclePurchaseOrder.qvd;
SET PKexp = PurchaseOrderID;
SET DailyQVD = $(DailyQvd)VES_VehiclePurchaseOrder&'_'&$(vDate).qvd;
SET QVD_HEADER = VES_VehiclePurchaseOrder;
SET TEMP_HEADER = VES_VehiclePurchaseOrder_TEMP;
//to_timestamp(LAST_UPDATE_DATE,'YYYY-MM-DD HH24:MI:SS')>=;

IF (weekday(today())<>'Sun') THEN

$(QVD_HEADER):
SQL SELECT *
   FROM CDMSLIVE.dbo."VES_VehiclePurchaseOrder" where CreatedOn >= '$(vLastReloadTime)' or UpdatedOn >= '$(vLastReloadTime)';

   store $(QVD_HEADER) INTO $(DailyQVD);


    $(TEMP_HEADER):
    LOAD DISTINCT *,
           $(PKexp) AS %PK
    RESIDENT $(QVD_HEADER);
//STORE $(TEMP_HEADER) INTO $(TEMP_HEADER); THIS CODE ADDED WHEN NEED TO TEST THE
//DELTA RECORDS ONLY MEANS EXACTLY HOW MANY RECORDS ARE GETTING POPULARED
CONCATENATE('$(TEMP_HEADER)')

    LOAD *
    FROM $(QVDFILE) (Qvd) WHERE NOT EXISTS(%PK,PurchaseOrderID); ///////THIS PORTION NEED TO BE EDITED EVERY TIME
DROP FIELD %PK;

STORE $(TEMP_HEADER) INTO $(QVDFILE);
DROP TABLE $(TEMP_HEADER);

ELSE

$(QVD_HEADER):
SQL SELECT *
FROM CDMSLIVE.dbo."VES_VehiclePurchaseOrder";
STORE $(QVD_HEADER) INTO $(QVDFILE);

END IF

DROP TABLE $(QVD_HEADER);

Thanks,

Deepak

Re: Incremental load

deepakqlikview_123 — Fri, 21 Mar 2014 04:05:32 GMT

Hello Expert,

Can you please help me out in this.

Thanks

Re: Incremental load

rwunderlich — Fri, 21 Mar 2014 05:21:12 GMT

I think you need to be ore specific in your question. The code you posted gives the result you want? Or does not? Or you get an error message?

Or it works fine but you want it explanation as to what it does?

Re: Incremental load

deepakqlikview_123 — Fri, 21 Mar 2014 07:05:13 GMT

Hello Rob,

It is working fine I just need expanation about how this work.

Thanks

Re: Incremental load

Fri, 21 Mar 2014 07:33:56 GMT

Hi Deepak,

Here it is as far as my understanding goes -

The script has a Reload history table (DELTA_PSA) which records your reload history.

The first instance of $(QVDHEADER) extracts records created and updated after your last reload and stores the extract as a Daily QVD with Current date appended to the QVD Name.

The $(TEMP_HEADER) table is loaded in memory using $(QVDHEADER) or the daily extract. To this table, your last consolidated qvd is concatenated ($QVDFILE)) making sure there are no duplicate entries for the Primary Key. (This would ensure any fresh updated records from latest extract would replace old or outdated records from previous QVD).

The concatenated $(TEMP_HEADER) is then stored as the latest consolidated QVD.

The above procedure is run only on Day <> Sunday. On Sundays, it will do a full extract of the table and save it as a consolidated QVD.

Though, in your script you have not mentioned the way DELTA_PSA gets updated to record the reload time for current execution.

Hope this helps.

Rob Wunderlich can correct or add to the above assessment.

Rgds,