Incremental Load issues

Anonymous — Fri, 29 May 2015 01:48:04 GMT

I am doing an incremental load of a FileDate(date value) and FileTimeStamp(hh:mm value), but it is only keeping the 2 most recent entries and is erasing the oldest ones. This will be a great value for me to be able to control so my qvd doesnt grow, but I need to find out what that part of the Incremental Setup and Incremental Update is so I can adjust it.

Incremental Setup

//Manual Recon Timeliness incremental

SET MRTPKexp=autonumberhash128(FileDate);

LET MRTtable='ManReconTime';

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

SET MRTQVD_EXISTS=1;

ELSE

SET MRTQVD_EXISTS=0;

END if

IF $(MRTQVD_EXISTS) THEN

maxdateTab:

LOAD max(FileDate)

From $(MRTQVDFILE)(qvd);

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

DROP table maxdateTab;

ELSE

LET INCREMENTAL_EXP='';

END IF

Incremental Update

//Manual Recon Completeness incremental

Directory;

IF $(MRCQVD_EXISTS) THEN

RIGHT JOIN ($(MRCtable)) LOAD DISTINCT

,$(MRCPKexp) as MRCPK

RESIDENT $(MRCtable)

;

CONCATENATE ($(MRCtable)) LOAD * FROM $(MRCQVDFILE) (qvd)

WHERE NOT exists(MRCPK, $(MRCPKexp))

;

DROP FIELD MRCPK;

END IF

Date Load

$(MRTtable):

LOAD FileDate,

Date(FileDate,'MM/DD/YYYY'),

FileTimeStamp,

Timestamp(FileTimeStamp,'hh:mm')

FROM

[..\..\MEC Snapshot\Data\Manual_Recon_Flexi_Upload.xlsx]

(ooxml, embedded labels, table is Matched, filters(

Remove(Col, Pos(Top, 9)),

Remove(Col, Pos(Top, 8)),

Remove(Col, Pos(Top, 7)),

Remove(Col, Pos(Top, 6)),

Remove(Col, Pos(Top, 5)),

Remove(Col, Pos(Top, 4)),

Remove(Col, Pos(Top, 3)),

Remove(Col, Pos(Top, 2)),

Remove(Col, Pos(Top, 1)),

Remove(Col, Pos(Top, 7)),

Remove(Col, Pos(Top, 6)),

Remove(Col, Pos(Top, 5)),

Remove(Col, Pos(Top, 4)),

Remove(Col, Pos(Top, 3)),

Remove(Col, Pos(Top, 2)),

Remove(Col, Pos(Top, 1)),

Remove(Col, Pos(Top, 4)),

Remove(Col, Pos(Top, 3)),

Remove(Col, Pos(Top, 2)),

Remove(Col, Pos(Top, 1)),

Remove(Row, RowCnd(Interval, Pos(Top, 3), Pos(Bottom, 1), Select(0, 0)))

));

As always, any help is much appreciated!

Dan

Re: Incremental Load issues

Fri, 29 May 2015 07:28:45 GMT

Hi,

I would suggest to keep the From_Date and To_Date in Table in Database and use select query to fetch value and hold in Variable in QlikView Script and use same for loading data incrementally.

you can schedule a Upadate query to Update your From_Date and To_Date each day before running the QlikView Job.

//DECLARE VARIABLE FROM_DATE_KEY & TO_DATE_KEY AS INT

SET V_FROM_DATE_KEY='INT';

SET V_TO_DATE_KEY='INT';

/*-----------------------------------------------------------------************---------------------------------------------------------------*/

//GET FROM & TO DATE FROM QLIKVIEW CONFIGURATION TABLE

QLIKVIEW_QVD_CONFIG:

SQL

SELECT

CONVERT(VARCHAR(10),[EXTRACT_FROM_DT],112) AS FROM_DT_KEY,

CONVERT(VARCHAR(10),[EXTRACT_TO_DT],112) AS TO_DT_KEY

FROM [QLIKVIEW].[QLIKVIEW_QVD_Config]

WHERE [TABLE_NAME]='STAR.FACT_TABLE';

//GET FROM & TO DATE FROM QLIKVIEW CONFIGURATION TABLE

/*-----------------------------------------------------------------************---------------------------------------------------------------*/

//ASSIGNE THE VALUES TO VARIABLES FROM_DATE_KEY & TO_DATE_KEY

LET V_FROM_DATE_KEY =Peek('FROM_DT_KEY',0,'QLIKVIEW_QVD_CONFIG');

LET V_TO_DATE_KEY =Peek('TO_DT_KEY',0,'QLIKVIEW_QVD_CONFIG');

DROP TABLE QLIKVIEW_QVD_CONFIG;

SELECT * FROM STAR.FACT_TABLE WHERE (DATE_KEY BETWEEN $(V_FROM_DATE_KEY) AND $(V_TO_DATE_KEY))

One time you have insert data into table [QLIKVIEW].[QLIKVIEW_QVD_Config] and keep updating EXTRACT_FROM_DT and EXTRACT_TO_DT before loading qlikview appliaction.

topic Re: Incremental Load issues in QlikView

Incremental Load issues

Re: Incremental Load issues