Where Not Exists() in Incremental load for Insert and Update case

[#1]

Hi All,

As we know for incremental load in qlikview , we have 2nd scenario where in newly inserted and updated records are present in latest qvd by Concatenating the older qvd and newly updated Source table.

Old table: Incremental_load

New Table : Incremental_load

Script used to concatenate the new records in source table with those already in QVD file.

//Load Incremental data

Incremental_Source_data:

LOAD Region,

     Id,

     Product,

     Sales,

    date(Modified_Date,'MM/DD/YYYY') AS Modified_Date

FROM

(ooxml, embedded labels, table is Sheet1) where Modified_Date > $(v_last_modified_date); //loading incremental records from source by comparing the modified date with last_modified_date variable

//Concatenate the updated records in source with those already in qvd

Concatenate

LOAD  Region,

     Id,

     Product,

     Sales,

     Modified_Date

FROM

(qvd) Where NOT Exists(Id);

Variable : v_last_modified_date stores the last updated date before the update in source file.

The issue lies with Not Exists(Id), where the 2nd last record for Id = 266 is still getting appended in resultant QVD file even after using Not Exists function.

Work around - Use Primary key that will resolve this issue. But if data source has no such properties like a text file then what is the resolution.

Please help.

[petter]

A text file can have a primary key if it has columns and any combination of some or all columns represents a uniquely identifiable row/line. The worst case scenario is that it is the entire row that is the key.

To make it easier to handle multi column keys the best practice is to use the Hash-functions in click. That will give a shorter fingerprint (unique) that you can use as the primary key or ID if you like.

Maxgro's answer in this post should give you some pointers:

Incremental load with Composite key

I would advice you to have a serious look at QVC - QlikView Components - Open Source and free to use script library that fully implements incremental load helper routines. You can find it on GitHub and also more information on the blog of Rob Wunderlich‌ the QlikViewCookbook.com

[petter]

[Peter_Cammaert]

I don't think it has much use to apply an Incremental Load on an Excel file.

Incremental loads are intended to improve performance when reading data from from tables with huge numbers of records (many millions) in remote databases (low bandwidth).

Reading a small file from disk using an incremental load doesn't offer any significant advantages.

Peter

[nagarjuna_kotha]

Hi Anuj,

ID was Same but Product and Date's are different that's the reason it is showing.ID=266 is Updated Record.

Regards,

Nagarjuna

[#6]

hi Peter,

Of course Incremental load is used to improve the loading time of fetching data from data base for very large dataset.

I have just taken few records in excel as an example.

[#7]

Nagarjuna,

Thanks for reply.

Its correct that each row is unique even if Id has duplicate entry.

But when we apply Not Exists(Id), Id is supposed to be distinct while concatenating data from both tables.

Unfortunately that is not happening at the moment.

[Peter_Cammaert]

Is Id in both files a numerical value without leading spaces? Sometimes invisible characters make seemingly identical values distinct.

There is an easy way to check this. In your document create a listbox for field Id. How many times does the value 266 appear in the listbox?

Peter

[#9]

Peter,

In source file (excel) Id is a Number and there are no leading spaces.

Automatically the second file i.e. qvd will have the same data type.

But Yes on creating a listbox i could not see the duplicate Id values.

[stevedark]

Hi Anuj,

Make sure you don't confuse the WHERE NOT EXISTS functionality and that of the DISTINCT key word.

If you do a WHERE NOT EXISTS on an incremental load field only one row for each unique value will come in from the new table.  It will not affect the rows that have already been loaded.  Usually people get caught out the other way with NOT EXISTS, in that they want it only to check the contents of a field before the load start, but it also checks values in the table being loaded (to get around this a temporary table or renamed field is required).

The DISTINCT function will remove totally duplicate rows from the table, and if issued with the first part of the load it will remove duplicates from both the initial table and the incremental one.  Note however, if there is a non distinct value in another field to the key then you will get duplicates in the key field.

You probably want to do a WHERE NOT EXISTS on both the first part and the second part of the load to achieve what you want.

You may find this post on incremental load useful for more pointers:

http://www.quickintelligence.co.uk/qlikview-incremental-load/

Cheers,

Steve