topic Incremental Load without Date and Pk fields in QlikView

Incremental Load without Date and Pk fields

nihhalmca — Tue, 09 Nov 2021 08:18:30 GMT

Hi All,

How can implement incremental load without date and primary key fields?

Thanks,

Regards,

Nihhal.

Re: Incremental Load without Date field

sushil353 — Fri, 24 Jul 2015 06:10:00 GMT

You can also check for primary key in your table to get increment data

Re: Incremental Load without Date field

Fri, 24 Jul 2015 06:15:51 GMT

create primary key and do increment laod on primary key

Re: Incremental Load without Date field

jonathandienst — Fri, 24 Jul 2015 06:35:10 GMT

You don't specify what type of incremental load you need.

The simplest is add-only incremental load. This requires some way of identifying new records - it does not have to be a date-added or date-modified field. If you have a increasing key value, you can identify new records using the key as well.This may be a primary key in the database table, but any continually increasing field value will work.

If you cannot identify newly added records, then add-only incremental load is not possible.

Re: Incremental Load without Date field

nihhalmca — Fri, 24 Jul 2015 07:51:00 GMT

Hi Jonathan,

Thanks for response.

My question is simple it is possible incremental load without date field and primary key?.

Regards,

Nihhal.

Re: Incremental Load without Date field

MK_QSL — Fri, 24 Jul 2015 08:19:23 GMT

For insert only Incremental Load, you need to have at least one of below

1) Last updated date or any date field
2) Sequential ID

Using above you can identify new records while extracting from the databased.

I also read a blog post from Barry Harmsen about creating a Hash Key from combinng the non-key fields, but never tried it. While extracting the new records, you need to check each key value and load only those values where the Hash Key if different. I suspect about performance issue on this. I would like to try it once at least. You can have a look!

http://www.qlikfix.com/2014/03/11/hash-functions-collisions/

You can use AutoNumberHash128(),AutoNumberHash256().

Hope this helps !

Re: Incremental Load without Date field

jagan — Mon, 27 Jul 2015 03:05:51 GMT

Hi Nihhal,

It is not possible to implement incremental loading without key (May not be primary key) and Date field. There should be some logic to find the records unique. The records may be unique by using multiple fields. If you attach some sample data then it would be easier to analyse and provide the solution.

Regards,

jagan.

Re: Incremental Load without Date field

qlikviewwizard — Mon, 27 Jul 2015 03:32:33 GMT

Hi nihhalmca

Can you attach some sample data for provide the sample data?

Re: Incremental Load without Date field

qlikviewwizard — Mon, 27 Jul 2015 03:36:46 GMT

Hi Check this thread.

Incremental Load

Re: Incremental Load without Date field

mohammadkhatimi — Mon, 27 Jul 2015 03:48:13 GMT

Hie...

PFA

Hope this will helps u..

Regards,

Mohammad

Re: Incremental Load without Date field

prabhas277 — Mon, 27 Jul 2015 04:06:03 GMT

without Date field we can't perform the incremental load.

Regards,

prabhas

Re: Incremental Load without Date field

jagan — Wed, 12 Aug 2015 15:54:20 GMT

Hi Nihhal,

If you got the answer please close this thread by giving the Correct Answer.

Regards,

Jagan.

Re: Incremental Load without Date field

qlikviewwizard — Thu, 13 Aug 2015 04:51:56 GMT

Hi Nihhal,

If you got the answer please close this thread by giving the Correct Answer.

Re: Incremental Load without Date field

its_anandrjs — Thu, 13 Aug 2015 05:08:00 GMT

Hi,

The mandatory requirement for the Incremental load is Primary key or the Last Modified date. With any one of this you can create the incremental load logic.

Regards,

Anand

Re: Incremental Load without Date field

balasundaram — Thu, 13 Aug 2015 05:09:09 GMT

Hi,

If there is no Date field, we cant go for incremental load.

Full load only Possible .

Re: Incremental Load without Date field

vardhancse — Thu, 13 Aug 2015 05:28:10 GMT

Hi,

If there is no primary key in our table, we can create one key by joining more than 1 field and dropping the original fields.

With the primary key and time stamp field only we can identify the records, for the scenario like add and modify the records.

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

Re: Incremental Load without Date field

infosurya2 — Thu, 13 Aug 2015 05:39:54 GMT

Hi Nihhal,

Without Date field & Primary key we can't perform Incremental Load,if you want to 'insert' new data by using 'Partial Reload' with ADD Prefix Keyword.

Regards,

Surya.

Re: Incremental Load without Date field

qlikviewwizard — Fri, 14 Aug 2015 09:10:53 GMT

Hi nihhalmca

Please close the thread by selecting Correct and Helpful answers. Thank you.

Re: Incremental Load without Date field

nihhalmca — Tue, 25 Aug 2015 13:23:18 GMT

Thanks Manish.

Re: Incremental Load without Date field

Anonymous — Mon, 11 Apr 2016 19:46:39 GMT

Hello Nihhal, can you share your sample data on which you want to perform incremental load?