Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Concatenate Incremental logic for Deleted records

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..

2 Replies
Gysbert_Wassenaar

It looks like you do this:

  1. Load data from the database
  2. Store the data into database.qvd
  3. Load data from database.qvd
  4. Load more data from database.qvd and concatenate it to the table created in step 3.
  5. 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:

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

talk is cheap, supply exceeds demand
Not applicable
Author

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.