Incremental Load in QlikView – Part2

In Incremental Load in QlikView – Part 1 we saw Incremental load with Insert Only option.

In this session, we will see how to do Incremental Load with

– > Insert and Update

 – > Insert, Update and delete

Insert and Update

Incremental Load with Insert and Update is similar to “Insert Only” (see my earlier blog Incremental Load in QlikView – Part1)

In this type of Incremental Load you will take new rows from the database and add/append  to the existing QVD.

In our previous excercise, we have loaded, the following rows/table in QlikView QVD

InitialTable_AddedOnerow

For this example, i will add 2 new rows into my existing database table

IncrementNewRows

Now our objective is to load only 2 new rows and not to overwrite the entire table

1. We will start with the inital load

Incremental1

2. Incremental load i.e. load new rows based on the LastUpdate date

Incremental_insertOnly_2

3. Concatenate. Here you will check for PrimaryKey and load only those rows where PrimaryKey is new.This will ensure that only new rows are added and existing rows are not overwritten

Incremental_LoadPrimary

 Insert, Update and delete

This option is similar to Insert and Update with only exception that you need to exclude/delete records that have been deleted from the source system.

To explain my point, i have deleted a row and added a new row in my database table. I have deleted the row where PrimeId = 6. I have added a row where PrimeId = 16. Now my database table looks like

IncrementNewRows_2

Now using Insert,Update and delete , you should be able to load the new rows and exclude the ones which are deleted in the source system and store the result into qvd.

Follow the same steps as above with Initial load,Incremental load,concatenate and then use Inner join script to exclude records that are deleted from the source system.

Incremental_InsUpDeleteLoad

After executing this load, you will see that new rows are added to your qvd and the row which was excluded from the db is deleted from your qvd.

If you select from the Qvd and create a Table box, it will look like the following, your qvd deleted the record for PrimeId = 6 and added a new record where PrimeId = 16

IncrementLoad_TableBox

Incremental loads are very useful when you have to load large transactional tables frequently and maintain the data integrity.

Further readingsquickintelligence

13 thoughts on “Incremental Load in QlikView – Part2

  1. Hi Chandra,

    Nicely explained. Very helpful. Appreciate your efforts in sharing your knowledge and helping all of us out.

    Cheeers!
    Pritam

    • Damitha:
      Not Exists is a QlikView function which you use as part of your Load script. Load command allows you to use QlikView specific functions.
      So it doesn’t matter if you are using SQL Server or Oracle or any other database.
      Hope this helps.

  2. Hi,
    I started Qlikview preparation recently. able to understand. can you kindly explain what type of work will be assigned to a qlikview developer in real time and what topics need to be stressed more while preparing for interviews

    • Vijetha:
      There are 2 parts to QlikView viz. QlikView designer and QlikView developer.
      QlikView designer is responsible for creating dashboards,graphs and tables using the underlying data model. QlikView designer should apply best practices of dashboard designing.
      QlikView developer is responsible for creating data model. Working on load scripts etc.
      These roles always overlap. QlikView developer may do dashboard designing as well.
      In my experience, a wholesome QlikView practitioner should have knowledge of creating data models and designing dashboards.He/She should also have exposure to QlikView server so that he can deploy dashboards on the server.

  3. Hi Chandra,

    can we update a record in qvd file. as per your example above for Insert and Update it is only inserting new records without overwriting the existing records. But if for eg any column value is updated for an existing row then that will not reflect in the qvd file. Is there a way around this?

    • Abishek:
      If the row has changed in the database that means it will be stored in the database with a new lastupdatedate and therefore will be treated as a new record.

  4. Hello Chandraish,

    I have two question with respect to this post.

    1. Since my data source is excel, the updation in excel file is not getting reflected in qlikview when I go for insert & update method

    2. How do I use SQL SELECT statement for excel table for doing inner join in ‘insert, update & delete’ method.

  5. Hi Chandra,
    i try an incremental load with Oracle, it’s ok.
    But the Exists function accept only 2 key Fields.
    My table have 3 key fields.
    I tried to create a composite key field on loading, but exists accept only real fields present on table.
    Do you have some other suggest ???
    Thanks

  6. have a small query. Hope you can help me on this.
    I have three different sources which I get as QVX file( Source1.qvx source2.qvx source3.qvx). I’m directly importing this qvx files into the qlikview. In these three sources I have common variables Year, Month , Day. Since Source1 is my main data. I created a drop down the qlikview for user selection. I’m looking for global variable when user selects year , month, day from the source1 the selection must be pass as parameters(year, Mon,Day) to source2 and source3 inputs.

    Sources: Source1, Source2, Source3
    Parameters to be passed : Year, Month, Day
    From: Source1 To: Source2,Source3

    Data: In source1
    Year Month Day
    2015 Jan 1
    2015 Jan 2.
    ………..
    ………….
    Could you suggest me solution this please.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.