topic Incremental Load Help on Script in QlikView

Incremental Load Help on Script

benhumphrey — Fri, 06 Feb 2015 09:01:22 GMT

Hi all,

I have a large transactions table that has millions of records in it. What i want to do is only reload the new records. Old records are static and never change. There is a transaction number that is unique to each record created in the database so i only need to reload the records that have a transaction number that doesnt already exist in the QVD. Eg

//Pulled from QVD already created

Transactions:

Date,

TransNbr,

Qty,

Time,

Type,

Locations;

//Load in new records where TransNbr doesn't exist in previous table

??????

I'm unsure on the exact script that i need to use as i haven't used incremental loads before.

Thanks for any help in advance

Ben

Re: Incremental Load Help on Script

prieper — Fri, 06 Feb 2015 09:06:17 GMT

Aircode:

Data:

LOAD id, .... FROM OldTable.qvd (qvd);

// add only records, for which the id is not known

CONCATENATE id, .... FROM NewTable .... WHERE NOT EXISTS(id);

STORE DATA into OldTable.qvd (qvd);

HTH Peter

Re: Incremental Load Help on Script

Fri, 06 Feb 2015 09:10:06 GMT

Dear ,

Use this code for incremental load (insert only)

Maxdate:

LOAD

DATIME

FROM ..\Data\tablename.qvd(qvd);

RESDENT:

Load

DATIME as ModDate

Resident Maxdate order by DATIME Desc;

Let MaxTimeStamp=Peek('ModDate',0);

Let MaxTIMESTAMP=Timestamp($(#MaxTimeStamp),'YYYY-MM-DD-hh.mm.ss.ffffff');

Drop Table RESIDENT,Maxdate;

Table:

Load

SQL SELECT * FROM tablename WHERE DATIME > '$(MaxTIMESTAMP)';

CONCATENATE

LOAD *

FROM

E:\Qlikview\Data\RawQVD\tablename.qvd(qvd) ;

STORE TableINTO E:\Qlikview\Data\\tablename.qvd(qvd) ;

Re: Incremental Load Help on Script

Fri, 06 Feb 2015 09:13:07 GMT

Hi,

For Update

use in where Clause ===== WHERE NOT EXISTS( KEY);

and for Delete

Inner join with the KEY from Database to updated QVD.

Regards

Vimlesh