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
