topic Re: Switch Between Full Load And Incremental Load in QlikView

Switch Between Full Load And Incremental Load

Thu, 12 Feb 2015 05:29:58 GMT

All,

I have small Qlikview Project. In my Script Editor window i made two tab one for Full Data Load And other for Incremental Load.

How I Switch between Full load and incremental load tabs in reloading time...?

Or Is there any other way to perform Full data load or Incremental load based on end user action..?

Regards,

Faisal

Re: Switch Between Full Load And Incremental Load

avinashelite — Thu, 12 Feb 2015 05:34:05 GMT

Hi Faisal,

first identify Based on which condition you want the incremental and full load.

Then use conditions functions like If and verify the condition, if it satisfy then direct it to increment or full load

Re: Switch Between Full Load And Incremental Load

Thu, 12 Feb 2015 05:42:05 GMT

Hi Avinash,

Thanks for the reply.

There is two options

1. Initial load will be full load and after that each reloading time 'incremental load' have to perform

2. End user can take decision whether he want full data load or Incremental load in dynamic way

Regards,

Faisal M A

Re: Switch Between Full Load And Incremental Load

avinashelite — Thu, 12 Feb 2015 05:47:04 GMT

I think this is not possible!! because how will you get the user input every time??? and it will be a manual work to get the user selection and reading that in the script is very tedious.

so you have to decide whether you want a full reload or only incremental load and design the same rest of the schedule reloading task will be taken care by QMC.

Re: Switch Between Full Load And Incremental Load

Thu, 12 Feb 2015 06:05:18 GMT

Then can you please explain, how will perform the first option.

Also Is there any way to perform second option using button or some thing

Like below

Regards,

Faisal

Re: Switch Between Full Load And Incremental Load

avinashelite — Thu, 12 Feb 2015 06:19:11 GMT

Ok see, in the button background you need to set a variable in the front end.

say the variable name vReload and set value using the action (see in the button properties) to Full load or Incremet

then in the script try like this:

let vReload_parameter=$(vReload);

if vReload_parameter='Full load'

then

your full reload script

....

else

your incremental reload script

Re: Switch Between Full Load And Incremental Load

Thu, 12 Feb 2015 06:45:04 GMT

Ok I Understand Thanks.

But one more question,

How we know in Reloading time, if it is first reload

Regards,

Faisal

Re: Switch Between Full Load And Incremental Load

jagan — Thu, 12 Feb 2015 07:52:23 GMT

Hi,

Check this link, hope it help syou.

Getting Input while reloading in Qlikview

Temp:

LOAD

Input('Enter Load Type', 'Input box') AS InputValue

AutoGenerate 1;

LET vLoadType = Peek('InputValue');

DROP TABLE Temp;

IF vLoadType = 'Full' THEN

// Your Full Load script goes here

ELSE

// Your Incremental Load script goes here

ENDIF;

Hope this helps you.

Regards,

Jagan.

Re: Switch Between Full Load And Incremental Load

jagan — Thu, 12 Feb 2015 07:53:00 GMT

Hi,

If user enter Full, then Full load is performed otherwise incremental load is performed.

Regards,

Jagan.

Re: Switch Between Full Load And Incremental Load

Thu, 12 Feb 2015 11:53:03 GMT

Hi Jagan,

When I open the link following error is coming

Also can you please explain more about Full Load and Incremental load.

What I want is First load will Full Load after all load would be Incremental load.

Which condition will give without input box or button...?

Regards,

Faisal M A

Re: Switch Between Full Load And Incremental Load

Thu, 19 Feb 2015 09:00:28 GMT

Please update me

Re: Switch Between Full Load And Incremental Load

jagan — Mon, 23 Feb 2015 02:33:04 GMT

Hi,

Check this link

Incremental Load Script

Regards,

Jagan.

Re: Switch Between Full Load And Incremental Load

qepmalheiros — Tue, 20 Oct 2015 19:15:17 GMT

Using QVD Files for Incremental Load

Incremental load is a very common task in relation to data bases. It is defined as loading nothing but new or changed records from the database. All other data should already be available, in one way or another. With QVD Files it is possible to perform incremental load in most cases.

The basic process is described below:

1. Load the new data from Database table (a slow process, but loading a limited number of records).

2. Load the old data from QVD file (loading many records, but a much faster process).

3. Create a new QVD file.

4. Repeat the procedure for every table loaded.

The complexity of the actual solution depends on the nature of the source database, but the following basic cases can be identified:

1) Case 1: Append Only (typically log files

2) Case 2: Insert Only (No Update or Delete)

3) Case 3: Insert and Update (No Delete)

4) Case 4: Insert, Update and Delete

Below you will find outlined solutions for each of these cases. The reading of QVD files can be done in either optimized mode or standard mode. (The method employed is automatically selected by the QlikView script engine depending on the complexity of the operation.) Optimized mode is (very approximately) about 10x faster than standard mode or about 100x faster than loading the database in the ordinary fashion.

Case 1: Append Only

The simplest case is the one of log files; files in which records are only appended and never deleted. The following conditions apply:

The database must be a log file (or some other file in which records are appended and not inserted or deleted) which is contained in a text file (no ODBC/OLE DB).
QlikView keeps track of the number of records that have been previously read and loads only records added at the end of the file.

Script Example:Buffer (Incremental) Load * From LogFile.txt (ansi, txt, delimiter is '\t', embedded labels);

Case 2: Insert Only (No Update or Delete)

If the data resides in a database other than a simple log file the case 1 approach will not work. However, the problem can still be solved with minimum amount of extra work. The following conditions apply:

The data source can be any database.
QlikView loads records inserted in the database after the last script execution.
A field ModificationDate (or similar) is required for QlikView to recognize which records are new.

Script Example:QV_Table:SQL SELECT PrimaryKey, X, Y FROM DB_TABLEWHERE ModificationTime >= #$(LastExecTime)#AND ModificationTime < #$(BeginningThisExecTime)#; Concatenate LOAD PrimaryKey, X, Y FROM File.QVD;STORE QV_Table INTO File.QVD;(The hash signs in the SQL WHERE clause define the beginning and end of a date. Check your database manual for the correct date syntax for your database.)

Case 3: Insert and Update (No Delete)

The next case is applicable when data in previously loaded records may have changed between script executions. The following conditions apply:

The data source can be any database.
QlikView loads records inserted into the database or updated in the database after the last script execution
A field ModificationDate (or similar) is required for QlikView to recognize which records are new.
A primary key field is required for QlikView to sort out updated records from the QVD file.
This solution will force the reading of the QVD file to standard mode (rather than optimized), which is still considerably faster than loading the entire database.

Script Example:QV_Table:SQL SELECT PrimaryKey, X, Y FROM DB_TABLEWHERE ModificationTime >= #$(LastExecTime)#; Concatenate LOAD PrimaryKey, X, Y FROM File.QVDWHERE NOT Exists(PrimaryKey); STORE QV_Table INTO File.QVD;

Case 4: Insert, Update and Delete

The most difficult case to handle is when records are actually deleted from the source database between script executions. The following conditions apply:

The data source can be any database.
QlikView loads records inserted into the database or updated in the database after the last script execution.
QlikView removes records deleted from the database after the last script execution.
A field ModificationDate (or similar) is required for QlikView to recognize which records are new.
A primary key field is required for QlikView to sort out updated records from the QVD file.
This solution will force the reading of the QVD file to standard mode (rather than optimized), which is still considerably faster than loading the entire database.

Script Example:

Let ThisExecTime = Now( );

QV_Table:

SQL SELECT PrimaryKey, X, Y FROM DB_TABLE

WHERE ModificationTime >= #$(LastExecTime)#

AND ModificationTime < #$(ThisExecTime)#;

Concatenate LOAD PrimaryKey, X, Y FROM File.QVD

WHERE NOT EXISTS(PrimaryKey);

Inner Join SQL SELECT PrimaryKey FROM DB_TABLE;

If ScriptErrorCount = 0 then

STORE QV_Table INTO File.QVD;

Let LastExecTime = ThisExecTime;

End If

QlikView 11.20 SR6