topic Re: Re: Incremental load problem in QlikView

Incremental load problem

Tue, 01 Oct 2013 00:31:20 GMT

// step 1. Load a QVD file

NoConcatenate

Temp_Decrypt:

Load

From $(vFullFileName)(qvd);

//step 2. Load latest data from Database

NoConcatenate

Temp_Incremental:

LOAD

SQL Select *FROM vResTable

where TimeModified <= '2013-09-13';

// step 3: Concatenate for incremental load

Concatenate (Temp_Incremental)

LOAD

Resident Temp_Decrypt: // with resident table

where not Exists (ID);

//step 4: store

STORE Temp_Incremental into $(vFullFileName);

The problem is

in step 5 it stores only data retrieved in step2 i.e. it is not concatenating with data retrieved in step 1. I do not have option to avoid step 1 and directly use the QVD file used in step3:

If I avoid step1. and directly use QVD file in step 3 then everything works fine.

Re: Incremental load problem

sasikanth — Tue, 01 Oct 2013 03:42:32 GMT

Hai Ramya I am sending one doc refer it ......this may help to solve your problem...

Re: Incremental load problem

rwunderlich — Tue, 01 Oct 2013 04:30:14 GMT

"If I avoid step1. and directly use QVD file in step 3 then everything works fine."

Correct. That is the way it's supposed to work. You should load the QVD data only after loadin ghte updated rows from the DB.

-Rob

Re: Incremental load problem

Tue, 01 Oct 2013 06:37:15 GMT

Hello Rob,

could you provide me any QVW for incremental load for fact table.

Regards,

Ram

Re: Incremental load problem

kaushiknsolanki — Tue, 01 Oct 2013 06:41:05 GMT

Hi,

You can go on

http://robwunderlich.com/

And in download section you can download the cookbook, which has a good numbers of example.

Regards,

Kaushik Solanki

Re: Incremental load problem

amars — Tue, 01 Oct 2013 07:43:12 GMT

Hi Ram,

Try like below, this should work

//step 1. Load latest data from Database

NoConcatenate

Temp_Incremental:

LOAD

SQL Select *FROM vResTable

where TimeModified <= '2013-09-13';

// step 2. Load all data except data loaded previously from the QVD file

Concatenate (Temp_Incremental)
Load

From $(vFullFileName)(qvd)

where not Exists (ID);

//step 3: store

STORE Temp_Incremental into $(vFullFileName);

Thanks...

Re: Incremental load problem

Tue, 01 Oct 2013 14:48:26 GMT

Hi Rob,

My data is encrypted in QVD file so I can not directly use data from QVD file. I need to bring it to cache and decrypt and then concatenate it. That's why I have step 1 where i decrypt data.

My question is

isn't it possible to concatenate two resident tables?

what's difference does it make whether data is coming from QVD or Resident table in step 3?

the other way I made it make it work by dumping my decrypted data in another QVD file and use that QVD file in step 3. but that way...i am exposing my encrypted data for a brief moment which i want to avoid.

Re: Incremental load problem

Tue, 01 Oct 2013 14:52:46 GMT

Hi Michael,

I can not avoid step 1 where i load my decrypted data from QVD file in resident table. My data is encrypted in QVD file and i can not perform concatenation on that.

I have omitted the decrypt function name from my code but you can make out from then name of the resident table.

I want to do the concatenation process with my resident table which has decrypted data. Is there any limitation of Qlikview that it can not do concatenation for incremental load with resident table.

Saurabh

Re: Re: Incremental load problem

rwunderlich — Tue, 01 Oct 2013 16:24:34 GMT

You can concatenate however you want. The problem you are having is in step 3:

where not Exists (ID)

All the ID values were loaded in step 1, therefore the test will never be true. You need to make a copy of ID and test against that.

In Step 2:

LOAD *, ID as ID2...

In Step 3:

where not Exists (ID2,ID)

Before the STORE

DROP FIELD ID2;

-Rob

http://masterssummit.com

http://robwunderlich.com

Re: Incremental load problem

Tue, 01 Oct 2013 19:46:34 GMT

It works.

Thanks

but why did you drop Field..why is it needed?

Re: Incremental load problem

rwunderlich — Tue, 01 Oct 2013 22:36:06 GMT

Field ID2 was created just as a place to collect the ID values loaded in Step 2. ID2 is only needed for the WHERE NOT EXISTS test in Step3, as a filtering mechanism. It is not needed after that and should not be kept in the final data model.

-Rob