Incremental Load Problem!

[#1]

Hi All,

I tried to do the incremental load with the help of the document which is given in the below link.

http://community.qlik.com/media/p/125837.aspx

But as per me it is not working . Requesting you to let me know what changes I need to do so that it works as expected.

Explanation about the problem given below:

~~~~~~~~

I tried the incremental load application with the data of mine. I am facing few issues when I was trying with my data. It is not working as expected. Explained about the problem in the screen shot which is given below. Hope you can look into it and provide some solution for the same.

I have attached the document which I was trying with.

~~~~~~~~

Thanks and Regards,

Rikab Kothari

[#2]

Hi Haneesh,

Thanks for your effort in explaining me in detail. But I am still not clear how the incremental update script tab works. If you can explain me then I think It can help me to understand the above post.

[#3]

Hi,

Please find the details below:

Directory;
// If incremental reload, load previous data and concatenate to data just read.
// Note that the composite primary key is *not* stored with the QVD. It is instead created
// on the fly. This allows us to use autonumber() which creates compact integer keys.
IF $(QVD_EXISTS) THEN
 // Create the PK for the incremental rows
 RIGHT JOIN ($(datatable)) LOAD DISTINCT
 *
 ,$(PKexp) as PK
 RESIDENT $(datatable)
 ;

The above script works as follows:

1. Checks if there is a QVD

2. If QVD exists, create a Primary Key from the existing new records and join it in turn with the same table

// Use CONCATENATE to explicitly name the table in case we've added any new fields.
 CONCATENATE ($(datatable)) LOAD * FROM $(QVDFILE) (qvd)
 WHERE NOT exists(PK, $(PKexp))
 //AND Date >= AddMonths(today(),-36) //?? Optional - roll data off the back
 ;
DROP FIELD PK; // Don't keep PK
END IF

STORE $(datatable) INTO $(QVDFILE);

The above script part does the following:

1. The data from QVD is concatenated with the newly loaded data.

2. While loading, it checks if the PK in new data matches with PK generated from the QVD, if so, that record is ignored and only the non matching records are concatenated. This ensures that all the updated records are loaded and the corresponding old existence are removed.

3. The temporary 'PK' field is dropped

4. The final data is loaded into the QVD.

These are the processes done in the script in 'Incremental Update' tab. Hope this is now clear to you.

-Haneesh

[#4]

Hi All,

One more which I am facing here is,

If I change some data in one of the rows and change the modified date as "12/05/10 5:39:24 . AM" and then again on the same row i am changing the data and the changing the modified date as "10/05/10 5:39:24 . AM" which is less than the previous one. In this case the row which I modified in the 2nd time is not getting loaded. If the I am giving the modified date higher than the first one then it is getting loaded. So let me know does it works as expected. Do I need to remember the highest modified date each time whenever I am modifying any rows and updating the date.

Please explain!

[#5]

Haneesh wrote:<blockquote><pre>
Directory;
// If incremental reload, load previous data and concatenate to data just read.
// Note that the composite primary key is *not* stored with the QVD. It is instead created
// on the fly. This allows us to use autonumber() which creates compact integer keys.
IF $(QVD_EXISTS) THEN
 // Create the PK for the incremental rows
 RIGHT JOIN ($(datatable)) LOAD DISTINCT
 *
 ,$(PKexp) as PK
 RESIDENT $(datatable)
 ;

The above script works as follows:

1. Checks if there is a QVD

2. If QVD exists, create a Primary Key from the existing new records and join it in turn with the same table

I am not clear with the above explanation either! Sorry for the trouble! I am very new!

Do you mean to say that table here is the one which is there in the Data Load tab of the script. If yes then let me know what the below one does. Does it refers to the same table which is said above? If yes then what is the use of joining with the same table. Is the used to generate the PK??? Please explain sorry for the trouble!

LOAD DISTINCT
 *
 ,$(PKexp) as PK
 RESIDENT $(datatable)

[#6]

Hi Haneesh,

Was I am understanding correctly which is left and which right table as shown below. Correct me If I am wrong!

Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4

Left Table	Right Table
Directory; LOAD dwrstkcode, stkaddress1, stkaddress2, stkaddress3, stkaddress4, stkcode, stkname, stkphone, stkstatus, stksubareaname, stockist_modified_on FROM DSC.xls (biff, embedded labels, table is Sheet1$) //WHERE ModDate >= $(MAX_DATA_DATE) // Using the maxdate we set earlier $(INCREMENTAL_EXP) ;	LOAD DISTINCT * ,$(PKexp) as PK RESIDENT $(datatable) ;