Incremental Load

[#1]

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!

[Anonymous]

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.

[#3]

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

[Anonymous]

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

[suniljain]

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

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

[johnw]

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.

[suniljain]

I am Agree with John.

[#8]

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.

[tanelry]

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