topic Re: incremental load with example pls? in QlikView

incremental load with example pls?

Sat, 08 Feb 2014 05:34:37 GMT

incremental load with example pls?

Re: incremental load with example pls?

MK_QSL — Sat, 08 Feb 2014 07:09:44 GMT

Dear Manoj,

If you search on community you will find many different examples of Incremental Load...

If you want to see the video with example, search on Youtube. There is a video of two parts from Shilpan Patel.

Also, ZIP file enclosed which contain sample file and apps for incremental load.

May be you need to change the file paths in scripts...

Hope this help...

Re: incremental load with example pls?

delmak2000 — Sat, 08 Feb 2014 10:34:28 GMT

Hi Manoj,

I am not a Qlikview pro, but i can recommend the attached zip file for your perusal.

I hope it helps!

Regards

Ayodele

Re: incremental load with example pls?

Peter_Cammaert — Sat, 08 Feb 2014 12:19:56 GMT

A few good reads copied from another discussion:

QlikView Reference Manual: Chapter 28.4 Using QVD Files for Incremental Load

Community: Re: Dynamic Update and Incremental Loading Gysbert provides a few useful links to discussions about Incremental Loading

Document: Incremental Load Strategies in this discussion: Re: QVD Files

Best,

Peter

Re: incremental load with example pls?

valerio_fatatis — Sat, 08 Feb 2014 14:37:12 GMT

Hi,

see the script:

Incremental Load

	Qualify *;
	// Using parametric file name. QVD and select source!

	set VAR_NAME_FILE_QVD='ONAIR_PROGRAM';

	let Listqvdexists=isnull(QvdCreateTime('$(VAR_DIR_ETL_QVD)$(VAR_NAME_FILE_QVD).QVD'));
	let day= right(date(today(1),'DD'),1);
	let VAR_LAST_RELOAD = 'to_date('&chr(39)&ReloadTime()&chr(39)&','&chr(39)&'DD/MM/YYYY HH24:MI:SS'&chr(39)&')';

	// I check the existence of the file or eno-QVD to figure out if I'm the first load!
	if $(Listqvdexists)=-1 then

	$(VAR_NAME_FILE_QVD):
	SELECT
	PROGRAM_ID,
	PROGRAM_NAME,
	PROGRAM_KIND_CODE
	FROM PROGRAM
	WHERE PROGRAM_KIND_CODE in (4270000,4270001);

	STORE * FROM $(VAR_NAME_FILE_QVD) INTO $(VAR_DIR_ETL_QVD)$(VAR_NAME_FILE_QVD).QVD;
	DROP TABLE $(VAR_NAME_FILE_QVD);

	//set to 5 so that 3 times a month 85, 15, 25) running the full forcibly!
	elseif $(day)=$(VAR_GG_FULL) then


	$(VAR_NAME_FILE_QVD):
	SELECT
	PROGRAM_ID,
	PROGRAM_NAME,
	PROGRAM_KIND_CODE
	FROM PROGRAM
	WHERE PROGRAM_KIND_CODE in (4270000,4270001);
	STORE * FROM $(VAR_NAME_FILE_QVD) INTO $(VAR_DIR_ETL_QVD)$(VAR_NAME_FILE_QVD).QVD;
	DROP TABLE $(VAR_NAME_FILE_QVD);

	Else

	// I need to be able to assess the necessary categories, sub a field and a date field change!

	$(VAR_NAME_FILE_QVD):
	SELECT
	PROGRAM_ID,
	PROGRAM_NAME,
	PROGRAM_KIND_CODE
	FROM PROGRAM
	WHERE PROGRAM_KIND_CODE in (4270000,4270001)
	AND MODIFICATION_DT >= $(VAR_LAST_RELOAD);

	UNQUALIFY *;

	Concatenate
	LOAD
	$(VAR_NAME_FILE_QVD).PROGRAM_ID,
	$(VAR_NAME_FILE_QVD).PROGRAM_NAME,
	$(VAR_NAME_FILE_QVD).PROGRAM_KIND_CODE
	FROM $(VAR_DIR_ETL_QVD)$(VAR_NAME_FILE_QVD).QVD
	(qvd)


	STORE * FROM $(VAR_NAME_FILE_QVD) INTO $(VAR_DIR_ETL_QVD)$(VAR_NAME_FILE_QVD).QVD;
	DROP TABLE $(VAR_NAME_FILE_QVD);

	end if

best regards.

valerio

Re: incremental load with example pls?

Anonymous — Sat, 08 Feb 2014 15:13:46 GMT

Hi Manoj,

Incremental load – it loads only the new or changed records from Database.

It can dramatically reduce the time needed to reload a qlikview application.

Basic incremental load process

Load new data from database table(slow, but few records)
Load old data from QVD file(many records,but fast)
Create new QVD file
Repeat procedure for each table

We have various scenarios for incremental load:

Daily Incremental reloads (Insert/Update)
Multi-QVD incremental reloads
Frequent incremental Reloads (Insert Only) –Utilizes binary load approach
Historical data management using Daily/Monthly Stacked QVDs
Incremental Reloads (Insert/Update/Delete) with deletion flag available.
Incremental Reloads (Insert/Update/Delete) with deletion flag available.

Basic Process:

QV_ILoad:

SQL SELECT PrimaryKey,X,Y FROM DATABASETABLE

WHERE ModificationTime>=#$(LastExecTime)#;

Concatenate

LOAD PrimaryKey,X,YFROM File.QVD;