Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Need help on Incremental Load for specific scenario

Hi All,

Could anyone help me in this case? . ? I have created a qvd for maintaining the Booking Status. Currently i am doing the Full Load every day from the database table. The data for this db table will be populated by Stored procedure from the fact table. I have two computed columns in the db table based on the current status. One is BookingStatus1st and another one is BookingStatusLatest. I am looking for the possibility to implement Incremental Load which should be qvd optimized. The problem in implementing IL is BookingStatus1st will remains same always but the BookingStatusLatest will get changed the whenever the booking gets the new status means the BookingStatusLatest will get changed always. if i implement IL how do the change the previous stored BookingStatusLatest column.

The structure of the Booking Status History table is as follows. The colored records the new records comes in IL. now i have two BookingStatusLatest in qvd which is not correct. Please help me in this

                                                                                        

BookingStatusIDBookingIDBookingStatusBookingStatus1stBookingStatusLatest
185021011TBCTBC-
528661011LIMBO--
3315291011CLOSED-CLOSED
newid1011CLOSED1-CLOSED1
185031012TBCTBC-
281741012LIMBO--
2719291012CLOSED-CLOSED
newid1012CLOSED1-CLOSED1

Thanks

Puni

1 Solution

Accepted Solutions
Not applicable

Re: Need help on Incremental Load for specific scenario

Puni,

Using QVD files for Incremental Load,

We have four scenarios like

1. Append Only.

2. Insert Only (No Update or Delete)

3. Insert and Update (No Delete)

4. Insert, Update and Delet

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_TABLE

WHERE 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_TABLE

WHERE ModificationTime >= #$(LastExecTime)#;

Concatenate LOAD PrimaryKey, X, Y FROM File.QVD

WHERE 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

4 Replies
Not applicable

Re: Need help on Incremental Load for specific scenario

Puni,

Using QVD files for Incremental Load,

We have four scenarios like

1. Append Only.

2. Insert Only (No Update or Delete)

3. Insert and Update (No Delete)

4. Insert, Update and Delet

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_TABLE

WHERE 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_TABLE

WHERE ModificationTime >= #$(LastExecTime)#;

Concatenate LOAD PrimaryKey, X, Y FROM File.QVD

WHERE 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

Not applicable

Re: Need help on Incremental Load for specific scenario

Hi Sasi,

Thanks for you suggestion. But i have specific business need as i said above. Please read my question again and see below.

After incremental load the data in qvd will be as following:

BookingStatusIDBookingIDBookingStatusBookingStatus1stBookingStatusLatest
185021011TBCTBC-
528661011LIMBO--
3315291011CLOSED-CLOSED
newid1011CLOSED1-CLOSED1
185031012TBCTBC-
281741012LIMBO--
2719291012CLOSED-CLOSED
newid1012CLOSED1-CLOSED1

The correct one would be like the following:

BookingStatusIDBookingIDBookingStatusBookingStatus1stBookingStatusLatest
185021011TBCTBC-
528661011LIMBO--
3315291011CLOSED--
newid1011CLOSED1-CLOSED1
185031012TBCTBC-
281741012LIMBO--
2719291012CLOSED--
newid1012CLOSED1-CLOSED1

Thanks

Puni

Re: Need help on Incremental Load for specific scenario

Hi,

Check this too

Incremental Load Script

Regards

ASHFAQ

danny4202001
Contributor

Re: Need help on Incremental Load for specific scenario

Hey Sasi,

I appreciate your explanation but when I try to execute the last scenario

I am facing below error

Capture.PNG

Community Browser