topic Re: incremental load in QlikView

incremental load

ankit777 — Wed, 09 Apr 2014 10:47:37 GMT

Hi all,

I have a table with two columns Name and adress. How can I apply incremental load for this.

In SQL we have 'in' function. Is there any similar function in qlikview.

Re: incremental load

yduval75 — Wed, 09 Apr 2014 11:54:10 GMT

In Qlikview, instead of 'in' function, you can apply match()

For example :

LOAD

Name,

Adress

From table1

where

Match(Country, "'France', 'Spain', 'USA'")>0

;

Re: incremental load

ankit777 — Wed, 09 Apr 2014 12:14:32 GMT

Can I use this for incremental load?

Re: incremental load

yduval75 — Wed, 09 Apr 2014 12:20:23 GMT

No, its' not for incremental load, it's to replace the "in" with sql

Here, a link to understand incremental load

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

Re: incremental load

Wed, 09 Apr 2014 14:59:04 GMT

For Incremental Load, you would need a Last Updated time in DB or any flag field. This way you can pull only latest record

Re: incremental load

Anonymous — Wed, 09 Apr 2014 16:01:39 GMT

I hope it helps you. You must detect a primary key and a data field in order to realize incremental loading.

The primary check is important to avoid double keys in your save

LET var_qvdexists=isnull(QvdCreateTime('$(var_yourpath)$(var_yourtable)'));

IF ($(var_qvdexists) = 0) THEN

TABLE:

LOAD max(date(floor(YOURDATE))) as MAXDATE

FROM $(var_yourpath)$(var_yourtable) (qvd)

;

let var_startdate = peek('MAXDATE');

let var_startdate = date(num(var_startdate), 'YYYY.MM.DD');

Drop table TABLE;

ELSE

let var_startdate = date(today() -1, 'YYYY.MM.DD');

END IF

//INCREMENTAL LOADING

BUFFER:

LOAD

YOURFIELDS,

%PRIMARYKEY

;

SQL SELECT *

FROM YOURTABLE

WHERE convert(nvarchar,YOURDATE ,102) >= '$(var_startdate)'

;

History:

load *, '1' as flag resident BUFFER;

IF ($(var_qvdexists) = 0) THEN

Concatenate

load *, '1' as flag

from $(var_yourpath)$(var_yourtable) (qvd)

WHERE NOT EXISTS (%PRIMARYKEY, %PRIMARYKEY)

;

ENDIF

drop field flag;

drop table BUFFER;

STORE History INTO $(var_yourpath)$(var_yourtable);

DROP TABLE History;

Re: incremental load

maxgro — Wed, 09 Apr 2014 17:34:28 GMT

something like this?

// load from the database

Name:

load *;

SQL select Name, Address from YourSqlTable;

// load from previously (usually day before) stored table

// skip existing Name

Concatenate (Name)

load

From Name.qvd (qvd)

where not exists(Name);

// store for next reload

store * from Name into Name.qvd (qvd);

Re: incremental load

Thu, 10 Apr 2014 06:28:47 GMT

Thank u.

Thanks and Regards

Amol Khochare