topic Re: Incremental load in QlikView

Incremental load

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I have a small doubt. When we do incremental load for fact table, will it be always full load? or incremental load

if it is a incremental load, since we don't have any updated date field how do we perform incremental load.

Can any one tell me how to proceed with this.

Regards,

Kumar

Re: Incremental load

swuehl — Fri, 15 Apr 2016 14:11:22 GMT

Sorry, I don't really understand your question, could you detail it a bit more?

Do you already know how an incremental LOAD approach is working? Using a historic QVD?

Re: Incremental load

Anonymous — Fri, 15 Apr 2016 14:22:07 GMT

Hi swuhel,

I have a fact table called orders and in that I don't have any date field. How to do a incremental load for this fact. Is it as same with like dimension table. or is it daily full load.

Re: Incremental load

swuehl — Fri, 15 Apr 2016 14:26:31 GMT

What is the source of your fact table? A file? Then you can maybe use a BUFFER LOAD prefix.

If it's a data base: Is there anything that can be used to determine new values? Like an auto-incremented OrderID?

Re: Incremental load

Gysbert_Wassenaar — Fri, 15 Apr 2016 14:26:59 GMT

If you want to do incremental loading then you have write code in the script that makes this happen. Qlikview will not automagically do incremental loading. And to make incremental loading possible your source data needs to have a field that can be used to determine which records have been added or changed since the last reload. Usually that's a date or timestamp field. It can also be a revision field that increases with each update. If you don't have any such field then you will have to compare the entire source table with the historical table stored in a qvd. In that case you might as well do a full load. See these blog posts for more information:

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

Incremental Load using Qlikview Components | Qlikview Cookbook

Re: Incremental load

diego_a_barboza — Fri, 15 Apr 2016 14:49:55 GMT

If the fact table is called "Orders", it is likely that has a order_id or order_number field which value is incremental. In that case, I recomend you to use it to limit the load for the fields on the next load, something like this:

// the first time you run this, you will have to manually define the value of the variable to avoid errors

// set vMaxOrderN =0;

Orders:

LOAD order_id,

item_descr,

item_descr

//include other fields here

FROM source_file.csv (txt)

where order_id > '$(vMaxOrderN)';

let vMaxOrderN = max('order_id');

// Whenever you want to make a full reload, just reset vMaxOrderN =0

Re: Incremental load

satishkurra — Fri, 15 Apr 2016 15:11:53 GMT

As Swuehl mentioned, we can use Buffer Load, if no update field exists in database table.

Qlikview buffer load

Re: Incremental load

Anonymous — Fri, 15 Apr 2016 15:12:21 GMT

Thanks Gysbert for sharing links. I am not able to understand pls provide me sample code for fact table as incremental load.

Re: Incremental load

Gysbert_Wassenaar — Fri, 15 Apr 2016 15:18:29 GMT

read the blog posts.

Re: Incremental load

Anonymous — Fri, 15 Apr 2016 15:18:54 GMT

My source is oracle data base.

Re: Incremental load

swuehl — Fri, 15 Apr 2016 15:21:45 GMT

Ok, but then again:

"If it's a data base: Is there anything that can be used to determine new values? Like an auto-incremented OrderID?"

Re: Incremental load

Anonymous — Fri, 15 Apr 2016 15:29:50 GMT

This table is associated with almost 11 dimension tables like account,lead,opportunity,time,campaign..

Re: Incremental load

Gysbert_Wassenaar — Fri, 15 Apr 2016 15:33:44 GMT

That's wonderful, but how is that relevant to identifying new and/or changed records in the Orders table?

Re: Incremental load

Anonymous — Fri, 15 Apr 2016 15:56:24 GMT

In fact table we have daily new records are coming based on source created date date.

Re: Incremental load

Gysbert_Wassenaar — Fri, 15 Apr 2016 16:02:25 GMT

So there is a date field after all. Is there also a field that uniquely identifies orders?

Re: Incremental load

jagan — Fri, 15 Apr 2016 16:07:23 GMT

Hi,

Also refer qlikview help file (F1) for very good examples,

I think in your case, Case 2 will work.

Sample script From Qlikview help file

Case 2: Insert Only (No Update or Delete)

If the data resides in a database other than a simple log file the case 1 approach will not work. However, the problem can still be solved with minimum amount of extra work. The following conditions apply:

The data source can be any database.
QlikView loads records inserted in the database after the last script execution.
A field ModificationDate (or similar) is required for QlikView to recognize which records are new.

Script Example:

QV_Table:

SQL SELECT PrimaryKey, X, Y FROM DB_TABLE

WHERE ModificationTime >= #$(LastExecTime)#

AND ModificationTime < #$(BeginningThisExecTime)#;

Concatenate LOAD PrimaryKey, X, Y FROM File.QVD;

STORE QV_Table INTO File.QVD;

(The hash signs in the SQL WHERE clause define the beginning and end of a date. Check your database manual for the correct date syntax for your database.)

Re: Incremental load

Anonymous — Fri, 15 Apr 2016 16:16:05 GMT

Hi Jagan,

Thanks for sharing sample code. Here you have mentioned in where clause

WHERE ModificationTime >= #$(LastExecTime)#

AND ModificationTime < #$(BeginningThisExecTime)#;

Modification time in my fact table as order_created_date and is having values as like below

For one order_key these many same dates are there.

Here one more thing can you explain me how do we get LastExecTime and BeginningThisExecTime.

Re: Incremental load

jagan — Sat, 16 Apr 2016 07:20:02 GMT

Hi,

Try like this

LET BeginningThisExecTime = Now();

Your incremental script goes here.

LET LastExecTime = BeginningThisExecTime ;

Hope this helps you.

Regards,

Jagan.