incremental load with example pls?

[#1]

incremental load with example  pls?

[MK_QSL]

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...

[delmak2000]

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

[Peter_Cammaert]

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

[valerio_fatatis]

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

[Anonymous]

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

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

We have various scenarios for incremental load:

1. Daily Incremental reloads (Insert/Update)
2. Multi-QVD incremental reloads
3. Frequent incremental Reloads (Insert Only) –Utilizes binary load approach
4. Historical data management using Daily/Monthly Stacked QVDs
5. Incremental Reloads (Insert/Update/Delete) with deletion flag available.
6. 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;