topic Incremental Load in QlikView

Incremental Load

Mon, 15 Nov 2010 02:16:28 GMT

Hi,

I am new to QlikView and I'm just wondering if a certain table doesn't have a date field as a reference that a table is modified or a new data has been added, what should I use to implement Incremental Load??

Thanks!

Incremental Load

Anonymous — Mon, 15 Nov 2010 02:23:57 GMT

There may be a unique key, some record ID field, that you can use. It would be convenient if it was a counter - 1,2,3,... In this case you simply load records with ID > than last loaded.

Incremental Load

Mon, 15 Nov 2010 03:40:57 GMT

what if a certain data is modified how can you tell that the data is modified using that unique ID??

Incremental Load

Anonymous — Mon, 15 Nov 2010 12:31:52 GMT

You cannot use ID in this situation. Need modification time.
Or, use a combination of fields as a unique ID:
Field1 & Field2 & ... & Field N as ID

Incremental Load

suniljain — Mon, 15 Nov 2010 13:17:22 GMT

you can also implement increamental load on primary key or composite key.

date and time is not a compulsory field for increamental load.

Incremental Load

johnw — Tue, 16 Nov 2010 01:05:45 GMT

You need to ask yourself these questions:

How can I identify new rows in this table?
Can rows be modified?
- If so, how can I identify modified rows in this table?
Can rows be deleted?
- If so, how can I identify deleted rows in this table?

Unless you tell us your table structure, we can't answer them for you. We can only give you guesses based on how tables are often structured (use an update timestamp, use a unique ID, use some sequential field).

If you can't figure it out, try posting your table structure.

Edit: Typically, you can't actually directly identify deleted rows in the table. You might be able to if you were using a delete status, or a deletion indicator, rather than actually deleting the rows. A typical approach if you've actually deleted rows is to take what's in QlikView and do an inner join on the unique keys loaded from the real table. Any keys that were deleted will then be removed from the QlikView table as well. In that case, you're identifying deleted rows by comparing to what's in QlikView, which should be the rows before the deletions occurred.

Incremental Load

suniljain — Tue, 16 Nov 2010 13:34:15 GMT

I am Agree with John.

Re: Incremental Load

Wed, 24 Aug 2011 07:40:11 GMT

Hi John,

I am trying to do an incremental load for a table having composite key. I create a primary_key while loading the incremental portion and also create a primary_key while loading from the qvd. The where not exists(primary_key) check gives the 'Field not found' error.

$(tableName):

LOAD name&surname as $(primaryKeyColumn),

name,

surname,

`modified_date`;

SQL SELECT *

FROM `qv_test`.names

Where $(lastModifiedColumn) > '$(startDate)' and $(lastModifiedColumn) < '$(endDate)';

Concatenate ($(tableName))

LOAD name&surname as $(primaryKeyColumn),

name,

surname,

`modified_date`

From $(qvdPath)$(tableName).qvd (qvd)

Where not (Exists($(primaryKeyColumn)));

Could you help me with how to get incremental load when there is a composite key.

Re: Incremental Load

tanelry — Wed, 24 Aug 2011 08:28:19 GMT

As error message suggests, the $(primaryKeyColumn) field does not exist in your QVD.

Create the QVD with that field first, then the script works.

Or use the Exists() with two parameters: Exists($(primaryKeyColumn), name&surname ). This will probably load slower (unoptimized).