topic Re: Concatenate Incremental logic for Deleted records in QlikView

Concatenate Incremental logic for Deleted records

Tue, 19 Aug 2014 20:37:00 GMT

Hi all,

I'm little bit stuck with a tricky Incremental logic.

The data i'm getting from the DB reside in the Table for Just one month or near to that.

After which the history records gets deleted from the Table permanently.

i want to append these records and concatenate with the latest records.

Below is the samle code i used with sample data.

I'm able to achieve the incremental portion that means the updated records are getting concatenated to the existing one.

But once some records are deleted from the table, i'm missing them in my qvd too.

Initial:

Database:

LOAD RP_LT_NUM,

RECEIVE_DATE,

RELEASE_DATE,

WP_LT_NUM

FROM

[..\ sample.xlsx]

(ooxml, embedded labels, table is [DATABSE]);

Outer join(Database):

LOAD INDUSTRY

MEASURE_DATE,

WP_LT_NUM

FROM

[..\WPLT_FIRST.xlsx]

(ooxml, embedded labels, table is [WPLT]);

STORE Database into Database.QVD(qvd);

DROP Table Database;

Max:

LOAD

Max(RECEIVE_DATE) as Max_DT

From

..:\Database.QVD

(qvd);

let vMax_DT=Date(Peek('Max_DT',-1,'Max'),'M/D/YYYY');

Incremental:

NoConcatenate

LOAD

RP_LT_NUM,

RECEIVE_DATE,

RELEASE_DATE,

WP_LT_NUM,

INDUSTRY ,

MEASURE_DATE

FROM

..:\Database.qvd

(qvd) Where RECEIVE_DATE>= $(vMax_DT);

Concatenate

LOAD RP_LT_NUM,

WP_LT_NUM,

RECEIVE_DATE,

RELEASE_DATE,

INDUSTRY ,

MEASURE_DATE

FROM

..:\Database.qvd

(qvd) Where not Exists(RECEIVE_DATE);

STORE Incremental into Database.qvd(qvd);

Thanks in advance..

Re: Concatenate Incremental logic for Deleted records

Gysbert_Wassenaar — Wed, 20 Aug 2014 12:37:54 GMT

It looks like you do this:

Load data from the database
Store the data into database.qvd
Load data from database.qvd
Load more data from database.qvd and concatenate it to the table created in step 3.
Store the data into database.qvd

The result of step 2 is that all the previously stored data in database.qvd is lost. So it never contains more records than what you last retrieved from the database. If records are deleted from the database they won't be stored in the qvd file. What you probably want is:

Load data from the database
Load the historical data from database.qvd and concatenate it to table created in step 1.
Store the complete set of current (updated) and historical date into database.qvd

Re: Concatenate Incremental logic for Deleted records

Mon, 01 Sep 2014 15:06:43 GMT

Thanks all..I got it. My bad..I did a silly mistake.

instead of appending to the the initial QVD, I was doing the alternate one.