topic Re: incremental load in QlikView

incremental load

gferran — Tue, 01 Mar 2016 16:01:50 GMT

Hi,

I'm loading about 150 milions registers from a QVD and adding more registers from a DB. To do it I propose two ways:

- inner join:

Option1:

load

sql select valid_registers from data_base;

inner join (Option1)

load

from file.qvd;

- Using a where clause:

let Filter=id_to_load

Option2:

load

from file.qvd

where match(a,Filter);

The first option runs ok but is necessary first to load all registers from qvd. I have created the second option looking for a better way to reload but the task doesn't finish.

Any comments?

Thanks

Gabriel

Re: incremental load

gferran — Tue, 01 Mar 2016 17:34:57 GMT

Hi,

I have forgot to say we have about 40 fields in the table.

Gabriel

Re: incremental load

maxgro — Tue, 01 Mar 2016 22:01:03 GMT

the fastest way from a qvd is with an exists because the load from the qvd is optimized

load

...... as a

from

...........;

load

from file.qvd

where exists(a);

Re: incremental load

jagan — Wed, 02 Mar 2016 01:52:54 GMT

HI,

Try using exists in where condition like this

Option1:

sql select DISTINCT valid_registers AS a from data_base;

inner join (Option1)

load

from file.qvd

WHERE Exists(a);

DROP TABLE Option1;

Regards,

jagan.

Re: incremental load

rwunderlich — Wed, 02 Mar 2016 04:49:40 GMT

‌The typical pattern in an incremental load is to load only new or changed rows in the SQL. Then when concatenating the existing QVD, use

where not exists(keyfield)

to exclude rows that were loaded in the SQL.

-Rob

Re: incremental load

Anonymous — Wed, 02 Mar 2016 05:02:22 GMT

load the data from database and give the where date > lastupdated date before this one to give conncanate