Incremental Load Retention Period

Anonymous — Tue, 16 Jun 2015 20:13:40 GMT

I am doing an Incremental Load and was wondering if there is a way to only retain a certain number of days of data since I am using a field named GLFileDate within the data to control what is loaded as a new or updated.

I would like to keep 35 days of data( based on the GLFileDate field - any suggestions on additions to the Incremental Scripts?

INCREMENTAL SETUP:

SET GLPKexp=autonumberhash128(GLFileDate);

LET GLtable='GLBalances';

If FileSize('$(GLQVDFILE)')>0 THEN

SET GLQVD_EXISTS=1;

ELSE

SET GLQVD_EXISTS=0;

END if

IF $(GLQVD_EXISTS) THEN

maxdateTab:

LOAD max(GLFileDate)

From $(GLQVDFILE)(qvd);

LET INCREMENTAL_EXP='WHERE GLFileDate>='&max('GLFileDate',-1);

DROP table maxdateTab;

ELSE

LET INCREMENTAL_EXP='';

END IF

INCREMENTAL UPDATE:

Directory;

IF $(GLQVD_EXISTS) THEN

RIGHT JOIN ($(GLtable)) LOAD DISTINCT

,$(GLPKexp) as GLPK

RESIDENT $(GLtable)

;

CONCATENATE ($(GLtable)) LOAD * FROM $(GLQVDFILE) (qvd)

WHERE NOT exists(GLPK, $(GLPKexp))

;

DROP FIELD GLPK;

END IF

STORE $(GLtable) INTO $(GLQVDFILE)(qvd);

Thanks- Dan

topic Incremental Load Retention Period in QlikView

Incremental Load Retention Period